SQL considerations and restrictions for COBOL

SQL support for COBOL has the several considerations and restrictions with the current implementation.

• A subset of SQL keywords is supported. There are SQL keywords that are currently supported by the IMS Universal JDBC driver but not supported when the SQL keyword is used in a COBOL application. For example:
  • XML is not supported by COBOL SQL in SELECT statements.
  • SQL COMMIT and ROLLBACK keywords are not supported. You should use IMS DB system services call to commit or roll back your database changes

    For information about the supported SQL statement and keywords, see SQL statements.

• SQL statements are supported for COBOL applications that are running in a BMP, IFP, and MPR for the IMS TM/DB environment and BMP is supported for the DBCTL environment. Batch and DB Batch are not supported. Also, the use of the EXEC SQLIMS API from IBM® CICS® Transaction Server for z/OS® and Db2 for z/OS stored procedures to IMS is not supported. The DL/I API should be used instead of the EXEC SQLIMS API.
• You can access Db2 for z/OS data using DB2® SQL support in the same COBOL application. IMS must connect to DB2 by using the SSM parameter, and the DFSLI000 module must be included.
• The IMS catalog must be enabled to use SQL support for COBOL. Make sure that you load the database metadata that would be needed by the COBOL SQL application into the IMS catalog.
• Ensure that you have sufficient storage for your IMS dependent region in your COBOL SQL applications. Specify at least 12MB for your IMS dependent region size for running a COBOL SQL application. You would encounter an 878 or other storage related abends if you run out of storage.
• Only one cursor and SQL statement can be active at a time in the application. If you must execute multiple SQL statements in the same application, you must first close the cursor for the previous statement and then open a new cursor or prepare a new statement.
• The set of SQL keywords that are supported in COBOL is only for database access calls. For IMS database services, GSAM, IMS TM, and message processing services, continue to use DL/I API.
• Dynamic SQL statement is supported. Static SQL is not supported currently.
• Only EBCDIC CCSID 37 and 1140 codepages for the COBOL CODEPAGE option are supported.
• The qualifiers for type Zoned Decimal or Packed Decimal can only be combined with other qualifiers by using the logical operator OR in a query
• For the predicate in a WHERE clause, when the data type is integer, no more than 10 consecutive AND statements are allowed due to potential extensive memory consumption. You can have more than 10 statements if the WHERE clause also contains one or more OR predicates, but not 10 consecutive AND statements.
• When you write a WHERE clause for an SQL statement and specify virtual foreign keys in the qualification statements, the following limitations apply when access to the database is random:
  • The virtual foreign key must be for one of the first child segments of the root segment (level 2). You cannot specify virtual foreign keys for segments that are at lower levels in the hierarchy.
  • The OR operator must be used to combine multiple qualification statements that use the virtual foreign key.
  • Only virtual foreign keys can be specified. You cannot specify other columns from the same segment or another segment.

  For example, assume that the HOSPITAL table is the root segment and the HOSPCODE column is the unique key. The WARD table is a first child segment of HOSPITAL and HOSPITAL_HOSPCODE is the virtual foreign key that references the HOSPCODE column in the HOSPITAL table. The WHERE clause in the following SELECT statement is valid:

  SELECT WARD.WARDNAME, WARD.PATCOUNT FROM PCB01.WARD
    WHERE HOSPITAL_HOSPCODE='ARS100100D' OR HOSPITAL_HOSPCODE='ARS100100D'
  

  The WHERE clause in the following SELECT statement will fail:

  SELECT WARD.WARDNAME, WARD.PATCOUNT FROM PCB01.WARD
    WHERE HOSPITAL_HOSPCODE='ARS100100D' AND XINTEGER > '5'