Using
Jazz with SQL and Micro Focus
Using Jazz with SQL and Micro Focus
Install
DB2 and SAMPLE database
Install
and Activate SAMPLE Database
Set
up the Micro Focus COBOL Project TSTSQL
Configure
Jazz to work with TSTSQL
Create
Jazz Record Descriptions
Configuring
MF Enterprise Developer to Compile the COBOL
Configuring
MFED to Run the Compiled COBOL.
Building
an RM Switch Module for DB2
Adding
ESDB2XA to Server MFJAZZ
Our own test environment is Micro Focus Enterprise Developer 3.0 with Visual Studio 2017, and we installed DB2 on to the laptop that is also the development computer for MANASYS Jazz. This describes what worked for us. Your set-up will be different, particularly if you are using Micro Focus in a team environment and others have already set up MF to work with your database, and we expect further differences if your SQL database is not DB2 but Oracle or SQL Server.
Install
DB2 and SAMPLE database
Install
and validate DB2
DB2 Developer-C for Windows (64 bit) was installed from this url.
· Installation was Typical,
· Domain: None – use local user account
· User name db2admin
· Unchecked [ ] Set up your DB2 server to set notifications
Otherwise everything was left to default. Data Services Manager (DSM) was not installed.
The installation was validated by opening the Administrator Command Window, and running command db2val. Results were: -
DBI1379I The db2val command is running. This
can take several minutes.
DBI1333I Installation file validation for the
DB2 copy DB2COPY1
was
successful.
DBI1337I The partitioned database environment
validation for the
instance DB2 was successful.
DBI1339I The instance validation for the
instance DB2 was
successful.
DBI1343I The db2val command completed
successfully. For details, see the log file
C:\Users\ADVANC~1\DOCUME~1\DB2LOG\db2val-Sat Aug 25 12_10_43 2018.log.
Install
and Activate SAMPLE Database
Run command db2sample and then activate: -
Then connect
and list active databases
List some
data from a table. SELECT Empno,
Firstnme, Lastname FROM Employee >C:\DB2PRNT.txt produces
EMPNO
FIRSTNME LASTNAME
------ ------------ ---------------
000010 CHRISTINE
HAAS
000020 MICHAEL
THOMPSON
000030 SALLY
KWAN
…
200310 MICHELLE
SPRINGER
200330
200340
42 record(s)
selected.
DB2 is now installed.
Set
up a Jazz Test Project
(Optional)
I chose to set up a separate project (in Jazz and MF) to test SQL, separately from the existing projects that were set up to use VSAM.
Set
up the Micro Focus COBOL Project TSTSQL
(See Set up Jazz for Micro Focus for details): -
· A Jazz project and Server called TstSQL were created.
· The server was configured with name TSTSQL,
o 64-Bit was NOT checked
o Template was CICSTemplate.xml as this offers both Batch and CICS
o Associated with project TstSQL
· Save the settings and build the solution
· Check that server TSTSQL is associated with project TSTSQL, and start it for the first time (reply [OK] to the security message)
· Prepare project tstJazz to work with Jazz
o Folder Catalog added to the project
o COBOL tab: COBOL dialect = Enterprise COBOL for z/OS, source format Fixed, Character set ASCII
o Debug tab: Active Settings = JCL
Configure
Jazz to work with TSTSQL
Workbench Options
(Tab 1)
Jazz will
already have been set up for your previous project, so while you want it to be
set up with its own project folder you probably want the same common folder and
subfolder structure so that you can change between projects simply by changing
the project folder. For example all my
Jazz projects are located from C:\tutorials\, where I have folders MFJazz1,
TSTSQL, and so on. Under each of these
project folders I use the standard subfolder names.
If this is what you want, follow Step 3 of Set up Jazz for
Micro Focus except don’t click [Default] to clear all folder names. Instead, edit the project path TSTSQL and
then click [Check Paths]. For the
project folder and subfolders click [Create] when prompted to create an empty
folder.
Click
[Initialise Project] and select all objects.
Click [Add Training] and add IN1.jzc, IN1R.jzc, IN1W.jzz and
Testdata.jzc.
Lang (Tab 2)
Check that
the COBOL dialect is correctly set to Enterprise COBOL for z/OS, and [P] Allow SQL is checked.
Test
environment should be IDE.
Click
[Check Folders] and create the COBOL Copy Books and optionally BMS
folders. For SQL we will need the COBOL
Copy Books folder, we won’t need the BMS Maps folder unless we are developing
3270-screen programs.
Jazz should
now be set up to work with server TSTSQL.
Test that everything is set up correctly by compiling and running the
basic batch program IN1W. See “Creating sequential file IN1” in “Creating Test
Data for Jazz Tutorials” for details.
Using
Jazz with DB2
So far we’ve set up Jazz to work with the TSTSQL server, and proven that everything works for a basic batch program. There is more that we need to do, both with Jazz and with the MF COBOL project TstSQL, in order that they work with SQL.
Configure
Jazz to use SQL
From the Jazz workbench, click [Configure], and then click the Lang tab. Check [ ] Allow SQL
Click the SQL tab and set the information required to connect to the database. Click [Test Connect] to check that everything is correct.
From a local DB2 install as above most of this will be obvious: the only field requiring special comment is “Schema”.
A relational database contains not only normal tables like EMPLOYEES containing data that we can retrieve and update, it contains System Catalog views which are views and tables used by the database to describe itself and manage access. We don’t want to see the SYSCAT views, so we give the schema name that our database uses for normal tables. You can list the database schema names with SELECT Schemaname FROM SYSCAT.SCHEMATA: -
SCHEMANAME
--------------------------------------
NULLID
ROBERTBW10
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
13 record(s) selected.
Why “ROBERTBW10”? This was the name assigned to the initial folder in C:\users\ by the vendor before I bought my laptop, and this name was picked up by the DB2 installation.
Create
Jazz Record Descriptions
To develop a program using Employee data we first need a Jazz description of the Employee table. To write it manually we’d have to know the definitions of each field. Or we could use Import from COBOL if we had a COBOL record layout. However the best way is to use Import from SQL, which is simple, fast, and accurate. Click here to find out how.
Jazz Programming
With Jazz definitions of the SQL tables we can develop programs that read and update data from the SQL database, just as we could from VSAM files. For example, here is a very simple program testing the functions of GET using the SAMPLE database: -
PROGRAM
SQLGet1 BATCH DATABASE sample DB2;
DEFINE
W DATA(
EMPNO CHAR(6),
EMPNBR PIC '999999' REDEFINES W.EMPNO);
COPY
JZSMth;
COPY
EMPLOYEE;
FOR
JZ.I = 10 TO 20 STEP 5; [only 10 and 20 exist]
W.EMPNBR
= JZ.I;
GET employee WHERE (EMPLOYEE.EMPNO = W.EMPNO);
PRINT(W.EMPNO, EMPLOYEE.FIRSTNME,EMPLOYEE.LASTNAME,
EMPLOYEE.SEX,EMPLOYEE.HIREDATE,EMPLOYEE.JOB);
END
FOR;
Like any other Jazz program, clicking [PROCESS] creates a COBOL program into the folder defined for generated COBOL: in this case folder \cbl in project TstSQL. There are some differences in this COBOL program because of its use of DB2: -
1. SQL Data must be known to both COBOL and SQL, so the definition of EMPLOYEE has been generated as COBOL into the COBOL copy library (TSTSQL\cpy). In its Data Division the program contains
001460 EXEC
SQL
SQLGet1
001470
INCLUDE SQLCA
SQLGet1
001480
END-EXEC.
SQLGet1
001490 EXEC
SQL SQLGet1
001500
INCLUDE EMPLOYEE
SQLGet1
001510
END-EXEC.
SQLGet1
In contrast, if Employee had been a VSAM record the record layout would have been generated directly into the program, without using a COPY statement in COBOL.
2. Instead of statements like READ that are used with VSAM, Jazz has generated EXEC SQL SELECT …
003750 JZ-16-GET. SQLGet1
003760* GET
employee WHERE (EMPLOYEE.EMPNO = W.EMPNO); SQLGet1
…
003800 EXEC
SQL
SQLGet1
003810
SELECT EMPNO, FIRSTNME, LASTNAME, HIREDATE, JOB INTO SQLGet1
003820
:EMP-EMPNO, :EMP-FIRSTNME, :EMP-LASTNAME, SQLGet1
003830
:EMP-HIREDATE:JZB-EMP-HIREDATE, :EMP-JOB:JZB-EMP-JOB SQLGet1
003840
FROM EMPLOYEE WHERE (EMPNO = :EMP-EMPNO) SQLGet1
003850
END-EXEC.
SQLGet1
The MFED project TstSQL needs to be configured so that these programs can be compiled.
Configuring
MF Enterprise Developer to Compile the COBOL
We need to give SQL properties for BOTH the project and the program.
First, the project.
· Open the MFED project (TstSQL)
· Open the Project Properties.
· Click on the SQL tab
1. Select ESQL Preprocessor HCO for DB2 LUW. This selects the preprocessor for DB2 for Linux Unix and Windows, and adds a directive DB.
2. Click the DB directive, and give the database name, SAMPLE, in the Value
·
Click the Dependency Paths tab. With SQL we’re using COBOL copy books for the
record layouts, and we’ve set up Jazz to put these into the \cpy
subfolder. Set this value (and click P
to check it): - 
Note: if we had checked 64-bit when server TSTSQL was created then presumably we would have browsed to C:\Program Files\IBM\SQLLIB\lib\db2api.lib. I didn’t experiment with a 64-bit server to confirm this guess.
Now the program. With the project (TstSQL) open
· When we process our Jazz program SQLGET1.CBL and SQLGET1.JCL were created, but although they are already in the appropriate folders they won’t yet be known to the MF project TstSQL. Add them to the project:
- Visual Studio/Solution Explorer,
- Right-click project TstSQL, click Add/Existing Item.
- Find SQLGET1.CBL (in the \cbl folder) and add it to the project
- Repeat for SQLGet1.JCL (in the \Local-JCL folder)
· Right-click program SQLGet1.CBL, click Properties
· Click the SQL tab
Either
· ESQL Preprocessor: select HCO or DB2 LUW
· Click directive DB, give value SAMPLE
The program’s property page is similar to the project property page above, with SQL directives: -
Or
· ESQL Preprocessor: select <Inherit from Project>
SQLGet1 compiles now, and we can try to run it by submitting SQLGet1.JCL.
Configuring
MFED to Run the Compiled COBOL
The first test run produces these results: -
1Printed at 22 Sep 2018, 15:25:06 RepNbr1 Page 1 0*EMPNO *-FIRSTNME-* *--LASTNAME---* SEX *HIREDATE *--JOB-* 0000010 00 *** 00 000015 00 *** 00 000020 00 *** 00 * * * END OF RepNbr1 * * *
Each
GET fails, returning SQLCODE = -1024, which means “A database connection does not
exist”. Not surprising because so
far because we’ve done nothing, either in the COBOL program or in our MF
project, to link program SQLGet1 to DB2 database SAMPLE
XA
Compliant Resources
For DB2 to work with an Enterprise Server we need to define a DB2 XAR. What’s an XAR? This MF Help page gives some background. The page starts by telling us that: -
Enterprise Server coordinates all XA activity, serving as an XA
Transaction Manager (TM).
XA stands for eXtended Architecture, and refers to the management of transactions that may occur across several computers. This DZone paper provides an explanation of distributed transactions and 2-phase commit. This distributed environment is relevant to programs using DB2 like SQLGet1: when SQLGet1 runs some of its processing occurs in a DB2 database that may be on our local computer or on a server somewhere, other parts occur in the simulated mainframe environment of Micro Focus Enterprise Server which might be our local test server, or a shared server, and so on.
The page continues
Enterprise Server works with XA-compliant Resource Managers (RMs) and
manages the execution of:
…
RM access is provided for: -
…
DB2 LUW
So we need to provide an appropriate XA-compliant Resource Manager for DB2 and define it to our MF Enterprise Server. Project TstSQL uses server TSTSQL: -
Examine the properties of TSTSQL: -
· Right-click Micro Focus Servers, click Administration
· Click [Edit] for server TSTSQL
· Click Properties/XA Resources to open the relevant tab.
Currently there are no XA resources defined, so the server will not be able to access our DB2 database. This explains why the program won’t run, and fails with SQLCode = -1024.
So we need to define a Resource Manager (RM) to allow us to use DB2, and define these to server TSTSQL.
Building
an RM Switch Module for DB2
This page, IBM DB2 RM Switch Module, tells us how to create a module that will be linked to our program so that when SQLGet1 issues a SQL command it is switched to the correct database. The page starts
>Start an
Enterprise Developer command prompt, 32-bit or 64-bit depending on the
application, running as an administrator
What does “Depending on the application” mean? Server TSTSQL has been created as 32 bit, and although DB2 is 64 bit we are using a 32 bit client because previously we set Link-to-lib to C:\Program Files\IBM\SQLLIB\lib\Win32\db2api.lib, so I thought that we wanted 32 bit. I tried this first: -
From
Windows Start, open
C:\Program Files (x86)\Micro Focus\Enterprise Developer\src\enterpriseserver\xa
Enter command Build DB2: -
This does not look good. I tried entering the command
SET
LIB=C:\Program Files\IBM\SQLLIB\lib\Win32\db2api.lib
but this merely changed the error messages. So I tried the 64 bit command prompt: -
Adding
ESDB2XA to Server TSTSQL
From the web page above: -
The build process
produces two binary files from the ESDB2XA.CBL COBOL source file:
|
Binary File |
Type |
|
ESDB2XA_S.DLL |
static |
|
ESDB2XA.DLL |
dynamic |
You can register an
RM switch module with Enterprise Server dynamically, or register it statically
depending on which binary file you use. We recommend that you use the dynamic
switch module option, ….
I found that these had been created in the folder
C:\Program Files (x86)\Micro Focus\Enterprise Developer\src\enterpriseserver\xa
I copied these to the folder
C:\tutorials\TstSQL\bin\x86\Debug
reasoning that it would be easier for the progam to link them from there. I don’t know if this was necessary: this step might have been unnecessary if I’d defined module differently (see below).
To define the XAR, click [Add] on the XA Resources tab (see above). Enterprise Server Administration Help/This Page says: -
Specify
a resource manager ID, used internally to identify a particular XA
configuration. This must be unique within the enterprise server. Mandatory.
Specify
the name by which the resource manager is known. It should match the name
returned in the name field of the xa_switch_t structure. Mandatory.
Specify
the location of the executable file (.dll on
Windows),(.sl on HP-UX PA-RISC, .so on other UNIX platforms) that contains
the entry point that returns the xa_switch_t structure to the enterprise
server. Mandatory.
Specify
the string that is passed to the resource manager on the xa_open() call. It usually contains at least
the database name, and the user ID and password for connecting to the database.
The contents of this string are database-specific.
Where
the resource manager supports dynamic registration, and the database vendor
requires the name of the module that provides the ax_reg function, you should specify casaxlib.
The
string that is passed to the resource manager on the xa_close() call. To find
out whether or not you need to supply Close
string, see your database vendor's documentation.
Specify
an optional text description of the resource manager definition.
Check
this to enable the resource manager. It is checked by default.
I set values like this:-
ID:DB2LUW I made this up, as long as it’s unique anything will do.
Name: ESDB2XA
Module: This gives the full path to the .dll. In retrospect I probably could have referred to the original items in C:\Program Files (x86)\Micro Focus\Enterprise Developer\src\enterpriseserver\xa
Open
String: From IBM knowledge center,
for my local database this should be DB=SAMPLE,UID=db2admin,PWD=Tilly123
Close string: I hadn’t come across anything suggesting that I needed one.
Description: can be any text
Now, with server TSTSQL running I was able to submit JCL to run job SQLGET1, either normally or in debugging mode within Visual Studio.