Using Jazz with SQL

Using Jazz with SQL. 1

Introduction. 1

Data Definition. 2

COBOL Concepts:  VSAM and SQL Definition and Retrieval 2

Jazz Definition Differences. 4

Optional Fields. 6

Introduction to Optional Fields. 6

Setting Optional Fields. 7

Optional Fields in Conditions. 8

Input and Output 8

PROCESS.. 8

GET. 8

WRITE, UPDATE, and DELETE.. 9

SQL Comparisons. 9

LIKE Comparisons. 9

IN Comparisons. 10

BETWEEN Comparisons. 10

Using NOT with SQL Conditions. 10

Setting up Jazz to work with SQL. 10

Jazz Configuration for SQL. 10

Configuring Your COBOL Environment – Micro Focus. 13

Configuring Your COBOL Environment – z/OS.. 13

 

To use SQL with Jazz requires Jazz Version 15.2 or later.

Introduction

At the COBOL level there are major differences between programs that use VSAM and programs that use SQL with relational databases like DB2, Oracle, and SQL Server.  For example this program reads VSAM records of customers living in a particular sales region.  However if CustF were a table in an SQL database then the program would look like this.  Jazz has minimised the difference: the Jazz program is essentially the same

*# Last Updated by JazzUser at 30/09/2018 2:52:27 p.m.

PROGRAM PrCustF BATCH DATABASE sample DB2;

COPY Custf;

COPY JZSMth;

PROCESS Custf WHERE CustF.Region <> 5 ORDER(CustF.Account);

    PRINT(CustF.Account,CustF.Region,CustF.District,CustF.Name,CustF.SalesThisMonth,

        CustF.Billingcycle);

END PROCESS custf;

 

The most obvious difference is the addition of the DATABASE option on the PROGRAM statement, but a more important difference is that in the first case we defined Customer as: -

            DEFINE CustF VSAM DATA( …);

and in the other

            DEFINE CustF SQL DATA( …);

There are some other differences in the definition: -

Account PIC '999999' has become Account CHAR (6) as PIC is not permitted as a DB2 data format.

Data set information, whether for an alternate index or the file is removed.  Databases manage such information themselves from the table name.  By writing

            DATABASE Sample

we’ve given all the information that the program requires.

 

In this case these are the only differences.   Differences would be greater if we’d used GROUPs, or dimensions in the VSAM file.

 

Of course not all differences can, or should, be hidden.  You will find that with SQL you have some extra options, and occasionally there are different rules. It is not the place of this document to discuss the advantages and disadvantages of using a relational database, our purpose here is merely to introduce these differences to you so that you can write programs as easily in one situation as the other, and take advantage of SQL’s special features. 

 

This chapter will assume that a relational database such as DB2, Oracle, or SQL Server has been installed and made available to you.  We have developed and tested the SQL features using IBM’s DB2 installed locally.  Click here to read what we did to install DB2.

 

We’ll start with the differences in definition.

Data Definition

COBOL Concepts:  VSAM and SQL Definition and Retrieval

In the VSAM version of program PrCust there is information about the Customer file in the COBOL program’s INPUT-OUTPUT section (see lines 000230 to 000280), a record layout is put into the File section (lines 000490 – 000600).

 

Before the program can read any data it executes

      003310     OPEN INPUT CustF.

 

Because the PROCESS statement includes ORDER, requiring that the data is read in sequence of ACCOUNT, the program uses an internal sort, reading data from the input, creating a shortened record of the required fields from the selected input records, and sorting this into ascending sequence: -

003680 JZ-Main-Program-Logic.                                           PRCustF

003690*    PROCESS Custf WHERE CustF.Region <> 5 ORDER(CustF.Account);  PRCustF

003700     SORT SORTWORK                                                PRCustF

003710         ON ASCENDING KEY Account OF JZ-SORTWORK                  PRCustF

003720         INPUT PROCEDURE IS JZ-16-PROCESSGroup-INPUT              PRCustF

003730         OUTPUT PROCEDURE IS JZ-16-PROCESSGroup-OUTPUT.           PRCustF

 

When the program wants to read a record from the Customer file it executes

003780 JZ-16-PROCESSGroup-INPUT1.                                       PRCustF

003790     READ CustF NEXT RECORD AT END MOVE 'Y' TO CustF-ENDFILE.     PRCustF

003800     IF CustF-STATUS IS NOT = '00' AND CustF-STATUS IS NOT = '10' PRCustF

003810         DISPLAY 'PROGRAM TERMINATED. STATUS CODE NOT 00 FOR READ PRCustF

003820-            'CustF.  Code=' CustF-STATUS                         PRCustF

003830         MOVE 'Y' TO CustF-ENDFILE                                PRCustF

003840     END-IF.                                                      PRCustF

003850     IF CustF-ENDFILE = 'N' AND Region OF JZ-CustF NOT = 5        PRCustF

After each VSAM operation the program will check the VSAM status code to ensure that there is no error, and to detect situations like end-of-file.

 

To run program PrCust the JCL must include a DD statement for Customer and for each path to its alternate indexes.  Jazz generates the following based on the DSNAME option of the DEFINE statement, and the path name given as part of Name’s DKEY property

//CUSTOMER DD DSNAME=IBMUSER.VSAM.CUSTF,DISP=SHR

//CUSTOME1 DD DSNAME=IBMUSER.VSAM.CUSTF1,DISP=SHR

 

If our program included more files there would be similar statements for each VSAM (or physical-sequential) file used by our program.  You can see similar entries for the Report file, and for the work files used by the SORT.

 

In the SQL version of program PrCust the File Control section has nothing about CustF, there is only an entry for the report file (unchanged). The record layout has been moved from the Input-Output section to Working Storage and is now in an external COBOL definition copied into the program with

001360     EXEC SQL                                                     PRCustF

001370         INCLUDE CustF                                            PRCustF

001380     END-EXEC.                                                    PRCustF

The I/O statements look very different.  First, with DB2 LUW the OPEN statement is replaced with CONNECT: -

003140     EXEC SQL                                                     PRCustF

003150         CONNECT TO :JZC-DATABASE                                 PRCustF

003160     END-EXEC.                                                    PRCustF

With DB2 ZOS there isn’t even this CONNECT statement: the JCL has already connected to the database before the program is initiated.

 

With SQL, instead of reading a complete record with READ, SQL defines the data that it wants from the database by declaring and opening a cursor, asking for the particular fields that it wants from the record. 

003480     EXEC SQL                                                     PRCustF

003490         DECLARE JZ-18-CURSOR CURSOR FOR SELECT Account, Region,  PRCustF

003500             District, Name, SalesThisMonth, Billingcycle FROM    PRCustF

003510             CustF WHERE Region <> 5 ORDER BY Account             PRCustF

003520     END-EXEC.                                                    PRCustF

003530     EXEC SQL                                                     PRCustF

003540         OPEN JZ-18-CURSOR                                        PRCustF

003550     END-EXEC.                                                    PRCustF

 

Sorting is simply part of the SQL SELECT statement, so that ORDER … does not require that Jazz creates an internal “record” with the required fields and pass it through a SORT with input and output routines

 

When a record is wanted and the VSAM version would have read a sorted record the SQL program executes

003600     EXEC SQL                                                     PRCustF

003610         FETCH JZ-18-CURSOR INTO :Cus-Account,                    PRCustF

003620             :Cus-Region:JZB-Cus-Region,                          PRCustF

003630             :Cus-District:JZB-Cus-District,                      PRCustF

003640             :Cus-JZ-Name:JZB-Cus-JZ-Name,                        PRCustF

003650             :Cus-SalesThisMonth:JZB-Cus-SalesThisMonth,          PRCustF

003660             :Cus-Billingcycle:JZB-Cus-Billingcycle               PRCustF

003670     END-EXEC.                                                    PRCustF

 

SQL knows a lot more about the data than VSAM.  SQL not only knows the record name, it knows the names and formats of the fields within it, and which of these fields are key fields and so can be used for indexed retrieval.  In Jazz we’ll continue to talk about a “Record” that contains “Fields”, but with a database more correct terminology would be “Table” and “Columns”.  You should understand that the records are not necessarily stored in the way that they would be with VSAM or a sequential file, with each field being arranged in order so that there is a record on external storage that looks like the record defined in COBOL storage as 01 CustF. …  

Jazz Definition Differences

The COBOL-level differences cause a few minor Jazz-level definition differences.

 

1.            Definition Name.  With VSAM the definition name (Customer) cannot be longer than 8 characters.  This is because it’s the name of a file known to zOS: there will be a DD statement

//CUSTOMER DD DSNAME=IBMUSER.VSAM.CUSTF,DISP=SHR

and the zOS rule is that DD names can’t be longer than 8 characters.

 

In contrast an SQL definition name may be much longer.  The rules depend on the database, but most allow at least 16 characters.  HOWEVER the definition must be saved in the COBOL Copy library where the 8-character restriction again applies, so Jazz provides the CPYLIBNAME option to allow you to specify a shorter name. 

DEFINE ArchivedCustomerData SQL CPYLIBNAME $SQ00004 DATA(

This definition will be saved locally (in the Jazz Copy Library) as ArchivedCustomerData, but in the COBOL Copy Library as $SQ00004.  Jazz will insert this option automatically, choosing a unique name starting with $SQ, if you do not give a short name when it is needed.

 

2.            Fields in a SQL record must be defined following SQL rules: -

DEFINE CustRec SQL DATA(

    Account CHAR(8),

    Name    VARCHAR(30),

    Address-Line1 VARCHAR(30),

    Address-Line2 VARCHAR(30));

   

Firstly, each of these field names is known to SQL.  The Jazz SQL definition may not include extra fields, including redefinitions and GROUPs

 

Fields may not have a dimension: thus Address(2) VARCHAR(30) would be invalid.

 

Formats like PIC that are not supported by SQL are not permitted.  For fields like EMPLOYEE.EMPNO, which is defined to SQL as CHAR(6) but actually has numeric values (in CHAR form), you can add a PIC property to the Jazz definition, for example

            EMPNO CHAR(6) PIC ‘999999’ REQUIRED KEY,

EMPNO remains a CHAR field, so you cannot use it in arithmetic.  You can however assign a numeric value to it, which will be assigned as characters in the PIC format, i.e. EMPLOYEE.EMPNO = 90; results in value ‘000090’, and a VALIDATE statement will report errors if its value is not numeric.

 

3.            Like a VSAM record, an SQL record may use properties KEY, DKEY, and UKEY to denote key fields.  This is strongly recommended as it helps Jazz to develop efficient code, but unlike VSAM it is not compulsory, and even when there are no key fields defined to Jazz you can write

            GET Custrec WHERE (condition)

 

Groups can’t be used with SQL to denote compound keys, so instead you use KEY PART n.  For example, here both AccountNbr and LineNbr are needed to uniquely identify the record: -

DEFINE RSQL SQL DATA(

    AccountNbr CHAR(12) KEY,

    LineNbr SMALLINT KEY PART 2,

    Line-data …

Similarly, DKEY and UKEY may use PART.

 

4.            Several Jazz data types are not directly supported by the database.  If you use these formats in an SQL definition then when you export a definition from Jazz to SQL then Jazz will map them to character fields in the database.  The unsupported data types and their SQL mappings are: -

Jazz format

SQL Mapping

PICTURE

CHAR(length)

BOOLEAN [DISPLAY]

CHAR(1).  ‘N’ = false, ‘Y’ = true

SMALLINT BOOLEAN

SMALLINT.  0 = false, -1 = true

GUID

CHAR(36)

TINYINT

CHAR(1)  *

BOOLEAN

CHAR(1)

MONEY

DECIMAL *

*           These types are mapped as shown for DB2 and Oracle, but for SQL Server will have the same type names as in Jazz

 

5.            In Jazz a field may use CODES, RANGE, MAX, MIN, and other properties that imply validation rules.  These rules will not be known to SQL.  If you use these properties, or one of the unsupported data types, then the underlying data is not validated outside your Jazz program, and if data originates externally then you should use ACCEPT statements to ensure that it is valid.  

Optional Fields

Introduction to Optional Fields

In SQL some fields may be able to have a value “NULL”, meaning that they have no value.  This is the default rule in SQL: fields that cannot have a NULL value are defined with the property NOT NULL: -

--DEFINE CustFS SQL  PREFIX Cus DATA(

--Account CHAR(6) HEADING 'Account Number' KEY,   

--Region LIKE Types.Region KEY PART 3,       

--District DECIMAL(3) RANGE(1:10) KEY PART 2,

--Name CHAR(40)  DKEY,       

--SalesThisMonth MONEY(7,2),   

--SalesYTD MONEY(7,2),        

--Billingcycle LIKE Types.Month,

--DateCommenced DATE DPIC 'dd mmm yyyy',

--Bool BOOLEAN);

CREATE TABLE CUSTFs

   (Account        CHAR(6) NOT NULL,

    Region         DECIMAL(3) NOT NULL,

    District       DECIMAL(3) NOT NULL,

    Name           CHAR(40),

    SalesThisMonth DECIMAL(7,2) /*MONEY*/,

    SalesYTD       DECIMAL(7,2) /*MONEY*/,

    Billingcycle   CHAR(1) /*TINYINT*/,

    DateCommenced  DATE,

    Bool           CHAR(1) /* BOOLEAN:Y/N*/,

    PRIMARY KEY(Account,Region,District))

 

For an example of why this might be useful, imagine that your Customer file records NbrChildren.  If you don’t know how many children a customer has you don’t want to set this field to zero, because knowing that a person has zero children is not the same as not knowing.  Yet if you set this to an artificial value, say -1, then every time you want to do something with NbrChildren then you’ll have to remember to handle the artificial value with special logic in your COBOL program to avoid errors such as calculating an average incorrectly.

 

The original designers of relational databases implemented a solution to this problem: for optional fields SQL provides an indicator field.  In SQL you can test whether an optional field is present or not by testing to see if the indicator field has value -1.  As for the field itself, it can have any value (including -1 if its format allows this). 

 

This can be a useful concept, but it can also be a source of programming error and confusion.  Jazz tries to make things as simple as it can.  All you should need to do is to define the field with the OPTIONAL property: -

DEFINE CustRec SQL DATA(

    Account CHAR(8) REQUIRED,

    Name    VARCHAR(30) REQUIRED,

    NbrChildren SMALLINT OPTIONAL,

   

Actually you don’t need to write OPTIONAL, it’s the default and is assumed if you didn’t write REQUIRED.

 

Most of the time you’ll use NbrChildren like any other field, ignoring the possibility of it being NULL.  A built-in function $NULL can be used for assignments and comparisons when you do need to distinguish real and NULL values.

 

Jazz REQUIRED corresponds to NOT NULL in SQL.   OPTIONAL corresponds to NULL (explicitly or default) in the SQL definition.  In a Jazz definition fields are OPTIONAL by default, unless REQUIRED is written with the DEFINE statement

DEFINE CustRec SQL REQUIRED DATA(

    Account CHAR(8),

    Name    VARCHAR(30),

    NbrChildren SMALLINT OPTIONAL,

 

Optional fields have a Null indicator and a data value.  SQL doesn’t define what is in the data value if the field is NULL, and when SELECT reads a VARCHAR field that has less than the maximum length it does not extend the value.  Jazz generates statements into the COBOL program to ensure that the data value is set to the default value (normally 0 or blank) when the field is NULL, and that VARCHAR fields do not retain the residue of earlier values when a short value is returned.   This enables the Jazz programmer to use OPTIONAL fields as if they were REQUIRED in most situations.

Setting Optional Fields

If you set an optional field with an assignment, e.g. Custrec.NbrChildren = value; then: -

Form of Value

Target (e.g. Custrec.NbrChildren) is set to

Note

value is a constant

Not NULL, data value from the value

 

Value is a normal field (not OPTIONAL

As above

 

Value is an optional field

Set to NULL or Not NULL from the value

 

Value is $Null

Set to NULL, data value set to default

1

Value is an arithmetic expression

Set to Not NULL, value calculated as normal

2

Value is a string expression

Set to Not NULL, data value = concatenation of string elements

3

Value is a coded field with a value

Set to Not NULL, data value set to the code or code value by normal rules

4

Value is a coded field that has not been set

Set to NULL, data value set to zero or blank

4

Value is an actual code

Set to Not NULL, and the actual code is used as the data value.  This applies even when the actual code is zero or blank.

 

 

Notes:

1.            If there is a VALUE property then this defines the default value.  If VALUE is not present, then

1.    BOOLEAN fields are set to False

2.    Other coded fields are set to “Code Undefined”.  The fields code will be zero or blank, the fields value will be ‘*****’.

3.    Numeric fields are set to ZERO,

4.    CHAR(n) PIC ‘picture’ fields are set to zero as characters in the form of ‘picture’.

5.    Other CHAR fields and VARCHAR fields are set to SPACE.  The length of VARCHAR fields is set to zero.

2.            If any of the numeric fields in the expression is NULL, then it will be treated as zero.  This does not make the calculated value NULL.

3.            If any of the string fields in the expression is NULL, then it will be treated as a zero-length character string, i.e. ‘’.  This does not make the concatenated value NULL

4.         Here is part of a definition

DEFINE SQLT1 SQL DATA(

    C CHAR(2) CODES (AK:Alaska,AL:Alabama),

    E CHAR(6) OPTIONAL,

    ….

Provided that C has been set, perhaps by an assignment like sqlt1.c = Alaska; then an assignment

sqlt1.e = sqlt1.c;

sets E to Not Null, and to value Alaska.   However E is set to NULL if the code has not been set or its value is not found: in these cases the data value will be set to ‘******”.

Optional Fields in Conditions

In normal Jazz programming – anywhere except I/O statements involving SQL - you can use OPTIONAL fields as you would any other fields.  If they are NULL, then they will have zero or blank values, and you can write statements like

            IF Field1 >= Field2 …

without having to think about whether either or both fields are NULL.  When you do want to distinguish between NULL and Not NULL values you can refer to $NULL.  With $NULL you can test for = (equal) and <> (Not equal), but not for > or < as $NULL has no value, and therefore has no order.

 

In SQL I/O statements the rules of SQL are applied, and so in WHERE, ORDER, etc you may need to be aware of the way that SQL handles NULL values. For example: -

PROCESS SQLTab WHERE (SQLTab.charfield = SQLT2.charfield) UPDATE

        ORDER (SQLTab.CharField, SQLTab.SIFld DESC);

 

To SQL a NULL field does not have a value at all, so its value can never be equal to, not equal to, greater than or less than any other value.  Even another NULL value!  So that

SQLT2.Charfield = $Null;

PROCESS SQLTab WHERE (SQLTab.charfield = SQLT2.charfield) …

returns NO RECORDS.  It DOES NOT return all the records from SQLTab with NULL values.  If you want these records then you write

PROCESS SQLTab WHERE (SQLTab.charfield = $NULL) …

Input and Output

Records in an SQL database are processed with the same statements as VSAM: PROCESS, GET, WRITE, UPDATE, and DELETE.  In some cases there are some extra options and a little more flexibility, but generally there is little difference.   PROCESS and GET will retrieve only the fields that are referenced in your program: thus if you have defined a SQL table with fields A, B, C, D, and E, but your program refers only to A, C, and E, then fields B and D are not retrieved from the database.  Within your program the fields B and D will have their default values, and if you insert a new record with WRITE or GET UPDATE then if these unused fields are OPTIONAL they will not be written to the database, while if they are REQUIRED then they will be written with their default values. 

PROCESS

When a PROCESS statement uses VSAM then the WHERE condition may only refer to key fields, and the conditions must be = or >=.   A SQL PROCESS is more flexible, allowing you to write WHERE conditions of any complexity and naming both key and non-key fields.  Of course if you abuse this flexibility you may find your program taking hours to run because you’re reading the entire database sequentially.  

 

You can use SQL operators BETWEEN, IN, and LIKE (or ~ meaning LIKE), as comparison operators.  Note that this is not actually a difference with VSAM (and sequential), as MANASYS Jazz allows these operators with other file types, and in general logic (IF statements etc).

GET

As with PROCESS, WHERE conditions are more flexible.  GET is intended for situations where you want to read one particular record: like VSAM, a SQL GET that is ambiguous returns the first record, and (in CICS programs) provides PF7/8 paging to get the previous and next record.  With SQL you can add an ORDER option to the GET to control which record is returned first.  Without this option (and in VSAM) it is not defined which record is “First”.

WRITE, UPDATE, and DELETE

As with VSAM these are usually implicit, with UPDATE options on PROCESS or GET statements causing the record to be updated when the next record is retrieved, or the program finishes. UPDATE and DELETE have two new options with SQL, WHERE and CURRENT.   CURRENT is used when the statement follows a PROCESS or GET, causing the record last read to be updated or deleted.  WHERE options allow sets of records to be processed in one operation: this may be very powerful, but it may also be risky as you can delete or update large sections of the database.

SQL Comparisons

An “SQL condition” is a condition that you write in a WHERE clause of an I/O statement for an SQL table.  It is similar to the WHERE condition that you can write for a VSAM file, or a condition that you write in an IF statement.  As in those cases

1.    you can use the normal comparison operators =, <>, >, >=, <, and <=,

2.    you can create compound conditions by combining simpler conditions with & (AND) and | (OR),

3.    you can use a reference to a BOOLEAN field without any comparison expression as a shorthand for “Boolean field = True”, and

4.    you can use ( ) to group parts of a compound condition, so that a test is evaluated in the correct order, e.g.

PROCESSWHERE ((C1 = ‘A’ | C1 = ‘S’) & Name = ‘John’); 

 

In an SQL condition there are also some further ways that you can express a condition.  These condition types may also be available in FOR, IF and ELSEIF conditions, and WHERE clauses for VSAM where the condition is used as a filter, not as a key expression.  See the notes with each type of special condition.

LIKE Comparisons

LIKE or ~ is a comparison operator that compares a string value against a string pattern.  For example,

            PROCESS SQLtab WHERE (SQLTab.charfield LIKE 'ABC%');

Here we are processing all records from SQLTab where the value of SQLTab.charfield starts with “ABC”.  Rules for using a LIKE comparison are: -

1.            The operator ~ may only be used in an SQL context, such as the WHERE clause of a PROCESS or other I/O statement accessing a SQL table.  SQLTab.charfield ~ 'ABC%' would not be valid if SQLTab were not defined with type SQL, or if this condition were written in an IF statement.

2.            The field, SQLTab.charfield, must have type CHAR or VARCHAR.

3.            The value, 'ABC%', may be a string constant (as here), or be a CHAR or VARCHAR variable.

4.            The value should contain at least one occurrence of the wildcard character, %, meaning “any character string, of any length.   Thus the comparison above means “SQLTab.charfield = 'ABC' followed by anything”, so the WHERE returns all records in which SQLTab.charfield starts with 'ABC'.   If you wrote SQLTab.charfield ~ '%ABC' this would mean “any value ending with 'ABC, while SQLTab.charfield ~ '%ABC%' would mean “any value containing 'ABC'.  There are also other wildcard options:  consult your database documentation for the rules that apply.  Jazz will check that a character string constant contains “%” and produce a message if it does not, but if the value is a variable it leaves any checking to SQL (DB2, Oracle, etc).

IN Comparisons

IN gives a list of values.  For example

            PROCESS SQLtab WHERE (SQLTab.charfield IN ('Australia', 'New Zealand', 'U.S.A.'));

This is equivalent to a series of conditions linked by |, for example: -

            PROCESS SQLtab WHERE (SQLTab.charfield = 'Australia' | SQLTab.charfield = 'New Zealand'

                                                            | SQLTab.charfield =  'U.S.A.'));

Either PROCESS statement will return records from SQLTab where Charfield has one of the three values given in the list.  Values may be constants, as here, or may field.

IN comparisons are valid in SQL WHERE conditions and in normal conditions (IF, ELSEIF statements), but not in WHERE conditions for VSAM unless the WHERE is a filter used in a BATCH program reading all records.

BETWEEN Comparisons

BETWEEN gives a value range.  For example

            PROCESS SQLtab WHERE (SQLTab.Balance BETWEEN 1000 AND 10000);

This is equivalent to: -

            PROCESS SQLtab WHERE (SQLTab.Balance >= 1000 &  SQLTab.Balance <= 10000);

 

The lower value should be given first: BETWEEN 10000 AND 1000 is always false.  As with IN, values may be constants or fields, and you can use NOT to negate the condition.  Note that AND is a keyword separating the two values, and is not the same as the boolean operator &.

 

BETWEEN comparisons follow the same rules as IN: they are valid in SQL WHERE conditions and in normal conditions (IF, ELSEIF statements), but not in WHERE conditions for VSAM unless the WHERE is a filter used in a BATCH program reading all records.

Using NOT with SQL Conditions

With LIKE, IN, and BETWEEN you may use NOT to negate the condition.   This PROCESS returns records from countries other than the three in the list: -

          PROCESS SQLtab WHERE (SQLTab.charfield NOT IN ('Australia', 'New Zealand', 'U.S.A.'));

 

You cannot use NOT with other conditions:  NOT = is an error, not an alternative way of writing <>.

Setting up Jazz to work with SQL

Jazz has been developed and tested with DB2 LUW (Linux, Unix, and Windows) and DB2 ZOS. Support for ORACLE and other databases will be developed on demand, or as time/resources permit.

 

First Jazz must be configured to develop COBOL and submit jobs using SQL.   The COBOL environment will also need to be configured to compile and run these jobs. 

Jazz Configuration for SQL

On the language tab you must check Allow SQL: -

 

Otherwise the DATABASE option (PROGRAM statement) and SQL definitions will cause error #602.

 

Next you should use the SQL tab to set default values for database access.   For DB2 LUW, leave [  ] zOS unchecked:-

 

 

1.    Choose a database type: DB2, Oracle, or SQL Server. 

2.    Name the database that you want to access

3.    Give the name of the schema within the database that contains your tables. 

4.    Give a user name and password with access rights to this database.  The user should have at least read and update rights to the database, for some activities database admin rights to create and modify tables will also be required. 

5.    If the database is installed on your own PC check Localhost, otherwise give the IP address of the server that will allow it to be found from your Jazz session. 

6.    Click [Test Connect].   Jazz will connect to the database, and check that there is at least one table defined within the schema.

 

For connecting to DB2 ZOS: -

For zOS the important information is in the lower section, SSID, Plan, collection, and DBRMLib.   Get these values from your systems programmer.  The earlier information, database name and schema, are relatively unimportant, but a database name is required for the PROGRAM statement in your Jazz program, and it has at least some documentation value although in a zOS system databases are really identified by SSID.

 

Jazz is now configured to process SQL programs and generate COBOL.  However for those COBOL programs to be compiled and run you have to configure your COBOL environment to support SQL. 

Configuring Your COBOL Environment – Micro Focus

Our own test environment is Micro Focus Enterprise Developer with Visual Studio, and we installed DB2 Community (a free version of DB2 LUW) on to a computer also used for Jazz software development.   Click here to learn more.

Configuring Your COBOL Environment – z/OS

When a Jazz program is compiled a job is created using JCL created from an appropriate JZL template.  Refer to the heading JZL Templates in the Workbench Help page for Configuration to learn about the rules of JZL.  There are several templates – Batch compile with SQL, Batch run with SQL, and CICS compile with SQL – that include SQL steps.   The templates that are supplied have been proven to work with SQL settings above using an Australian zOS system, but they will need editing for your set up.   Check these JZL templates, and correct them as necessary.