Published on February 20, 2014
Standard Access to IMS Data Access from COBOL Using SQL Haley Fung, IBM email@example.com
Disclaimer IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion. Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision. The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion.
IMS 13 SQL support for COBOL • Target Market – IMS TM and DB customers who would like to write or modify IMS COBOL applications to access IMS data using SQL • Challenge Addressed – No SQL access to IMS data from IMS COBOL applications – Lack of DL/I skills in AD community • Solution Statement – Enable SQL calls from COBOL applications in addition to the current Javabased solutions • Business Value – Expands IMS database access for application and database developers – Reduce application development cost by leveraging existing SQL skills – Provide a consolidated native SQL engine as the foundation of existing and future client exploitation
Prerequisites • Software requirements – IMS 13 + PTF UK98028 – Recent PTF: UI14549 – Upcoming APARs: PI10552, PI08209 – IMS Catalog function is required – COBOL compiler with IMS co-processor function • Enterprise COBOL Developer Trial z/OS V5.1 or • Enterprise COBOL for z/OS V5.1 + APAR PM92523 – Note: COBOL V5.1 requires z/OS 1.13 and above ! • Hardware requirements – Same as IMS 13 and COBOL V5.1
Solution Highlights • SQL support for COBOL – Offer SQL as a query language for COBOL programs to access IMS database in addition to DLI • SELECT/INSERT/UPDATE/DELETE – EXEC SQLIMS as the interface to execute IMS SQL calls ! • SQL processor in IMS – Process SQL calls natively by the IMS subsystem – Still perform DLI database call processing underneath – Provide a consolidated way for SQL processing – Uses database metadata in IMS Catalog ! • Support IMS TM/DB (MPP, IFP, BMP) and DBCTL BMP
Consolidated SQL processor • Consolidated SQL processor for both host (COBOL) and distributed applications • Data Provider for Microsoft .NET is now available with IMS Enterprise Suite 3.1 z/OS IMS SQL ODBA / DRA Distributed Language nterface .NET MPP BMP IFP COBOL JDBC DRDA Intended support V13 support ! Language Interface RYO SQL Catalog SQL processor Metadata DLI IMS DB
IMS SQL concepts
What is SQL? • SQL stands for Structured Query Language – SQL is a standard and is well defined – Most common database language for relational databases – Support for SQL can be found in programming languages such as Java, .NET, C++, COBOL, PL/I, etc • IMS and SQL – Introduced SQL support since IMS V7 for Java application – IMS V11 provided Open Database Solution allowing Java applications to access IMS databases over TCP/IP networks and off platform – Supports both data access and data manipulation with SQL. It maps hierarchical structures to relational concepts
Other Terminologies • Relational databases versus IMS databases – Table (relational DB) ! Segment (IMS DB) – Column ! Field – Row ! Segment Instance – Schema !PCB – Table primary key ! Segment unique key • Basic definitions – Statement - standardized set of commands used for data access, manipulation. Similar to executing a series of DL/I calls against a DB PCB. – Result Set – statements return a set of results based on the command executed. Similar to the IO Area returned on a DL/I call.
IMS 13 SQL support for COBOL
Statements – SQL to access data • Data manipulation – SELECT… FROM… to retrieve data – INSERT INTO… VALUES… to insert data – UPDATE… SET… to update data – DELETE FROM… to delete data – WHERE… AND… OR… to perform conditional selection of data
Statements – Declaration • Pre-compiler directives – DECLARE CURSOR, STATEMENT… to declare cursor, statement – INCLUDE SQLIMSCA, SQLIMSDA… to generate SQLIMSCA and SQLIMSDA structures – WHENEVER… to handle errors and warnings
Statements – Execution • SQL statement execution – For executing a SELECT statement • PREPARE … to parse SQL statement • DESCRIBE (optional)… to get resultset metadata • OPEN … to open cursor • FETCH … to fetch data • CLOSE … to close cursor processing ! – For executing an INSERT/UPDATE/DELETE statement • PREPARE… to parse SQL statement • EXECUTE… to execute the non-SELECT statement
Solution Details – Key application elements • Delimit SQL statement using EXEC SQLIMS ... END-EXEC • Dynamic SQL programming model – Must call PREPARE to process SQL statement • Host variables – Use for both to send and receive data processed by IMS • SQL communication area (SQLIMSCA) – Structure used by IMS to provide status feedback – SQLIMSCODE (error code), SQLIMSSTATE (state), SQLIMSERRM (error message) • SQL description area (SQLIMSDA) – Structure populated by IMS to provide result set metadata for a SELECT statement • Describe name, length, type of each column returned – Structure populated by application to provide input data
Solution Details – Dynamic SQL • Enable SQL statement to be constructed at runtime – No need to hard code SQL statement in the application – Segment and field names associated with the SQL calls are not known at compile time – SQL accepts as input in the form of a character string • Use Prepare call to process SQL statement – Parse SQL statement for syntax and semantics validation at runtime. No bind process. – Convert SQL artifacts to DLI – Statement can be prepared once and then execute many times
Coding - High-level application flow ● Declare host variable/structures for passing data between IMS and application ● Include SQLIMSCA for execution status ● Code SELECT, INSERT, UPDATE or DELETE statements to access IMS data ● Check SQLIMSCA for execution status ● Handle any SQL errors returned by IMS
Coding - SELECT • SELECT statements are used to query one or more tables – SELECT column1, column2, … FROM table_name WHERE some_column=some_value ORDER BY column1, column2 • Programming models – PREPARE… OPEN… FETCH (one or more)… CLOSE – Fixed-list SELECT • Returns rows that contain a known number of values of unknown type and size. You can specify a list of host variables to contains the filled values. – Varying-list SELECT • Returns rows that contain an unknown number of values of unknown type and size. You do not know the exact number and types of host variables to store the result values.
Coding Fixed-list SELECT 1. Include SQLIMSCA 2. Specify SQL statement in a COBOL variable 3. Declare host variable or structure for result data row 4. Declare a cursor for the statement name 5. Prepare the SELECT statement 6. Open the cursor 7. Fetch a row of data into host variable or structure. 8. Repeat previous step until not more data ▪ When no more data, SQLIMSCODE=100 9. Handle any error 10. Close the cursor
Sample COBOL SQL WORKING-STORAGE SECTION. * Declare SQLIMSCA EXEC SQLIMS INCLUDE SQLIMSCA END-EXEC. ! * Declare COBOL variable for SQL statement and result data 01 SQL-STATEMENT 49 SQL-STATEMENT-LEN PIC S9(4) COMP. 49 SQL-STATEMENT-TEXT PIC X(100). ! 01 HOSPITAL-RESULT-ROW 05 HOSPLL PIC S9(3) BINARY. 05 HOSPCODE PIC X(12). 05 HOSPNAME PIC X(17). ! PROCEDURE DIVISION. * Declare Cursor for the Prepared Statement EXEC SQLIMS DECLARE CURSOR cursor-name for prepared-statement-name END-EXEC. ! * Load SQL statement in the COBOL variable MOVE "SELECT * FROM PCB01.HOSPITAL” TO SELECT-STATEMENT-TXT.
Sample COBOL SQL (Cont’d) * Prepared SQL statement string for processing EXEC SQLIMS PREPARE prepared-statement-name FROM :SQL-STATEMENT END-EXEC. ! * Open Cursor EXEC SQLIMS OPEN cursor-name END-EXEC. ! * Execute SQL statement * Fetch data from IMS into host variable until no more data is found PERFORM FETCH-PROC UNTIL SQLCODE EQUAL 100. : FETCH-PROC. EXEC SQLIMS FETCH cursor-name INTO :HOSPITAL-RESULT-ROW END-EXEC. : * Close Cursor EXEC SQLIMS CLOSE cursor-name END-EXEC.
Coding Varying-list SELECT 1. Include SQLIMSCA 2. Include SQLIMSDA for result metadata 3. Specify SQL statement in a COBOL variable 4. Declare a cursor for the statement name 5. Prepare the SELECT statement 6. Call DESCRIBE to get result metadata 7. Declare and allocate storage for the result data variables 8. Open the cursor 9. Fetch a row of data into SQLIMSDA with result data variables 10. Repeat previous step until not more data ▪ When no more data, SQLIMSCODE=100 11. Handle any error 12. Close the cursor
SQL descriptor area (SQLIMSDA) • SQLIMSDA stores information about prepared SQL statements or host variables. • Can be read by IMS or the application – Read by application after a DESCRIBE statement for result set metadata – Read by IMS for the input host variables
Coding – INSERT/UPDATE/DELETE • INSERT – INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) • UPDATE – UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value • DELETE – DELETE FROM table_name WHERE some_column=some_value • Programming model – PREPARE… EXECUTE
Coding INSERT/UPDATE/DELETE 1. Include SQLIMSCA 2. Specify SQL statement in a COBOL variable 3. Declare host variable or structure for result data row 4. Declare a cursor for the statement name 5. Prepare the INSERT/UPDATE/DELETE statement 6. Execute the statement 7. Handle any error ▪ Check if SQLIMSCODE = 0
Coding – Parameter markers • A parameter marker is a question mark (?) that represents a position in a dynamic SQL statement where the application will provide a value – SELECT HOSPNAME FROM PCB01.HOSPITAL WHERE HOSPCODE = ? – INSERT INTO PCB01.HOSPITAL (HOSPCODE, HOSPNAME) VALUE (?, ?) • Programming Model – For SELECT statement, use USING clause with OPEN cursor • e.g. OPEN cursor USING :value1 – For non-SELECT statement, use USING clause with EXECUTE • e.g. EXECUTE stmt USING :value1, :value2
Handling errors • SQL communication area (SQLIMSCA) – Structure used by the database to provide status feedback – The SQL INCLUDE statement is used in the COBOL application to provide the declaration of the SQLIMSCA • The main elements in the SQLIMSCA are: – SQLIMSCODE – A return code represents a successful or failed SQL operation – SQLIMSSTATE – Common codes for error conditions which conform to the SQL standard – SQLIMSERRM – Error message text
Handling errors (cont’d) • The SQLIMSCODE is a return code for a successful or failed SQL operation. – If SQLIMSCODE = 0 and SQLWARN0 is blank, execution was successful. – If SQLIMSCODE = 100, "no data" was found. – If SQLIMSCODE > 0 and not = 100, execution was successful with a warning. – If SQLIMSCODE = 0 and SQLWARN0 = 'W', execution was successful with a warning. – If SQLIMSCODE < 0, execution was not successful. • IMS DL/I Status Codes will be shown in message text in some error cases
Compile and link IMS COBOL application source files with SQL statements COBOL Compiler with IMS coprocessor Libraries Translate EXEC SQLIMS Object files COBOL Link Executable Program INCLUDE DFSLI000
IMS coprocessor • Compile IMS SQL COBOL application with IMS coprocessor • Pre-process EXEC SQLIMS statements in COBOL source • Integrated with Enterprise COBOL V5.1 • Specify ‘SQLIMS’ compiler option to compile COBOL program with IMS SQL calls
Sample JCL for compile //********************************** //* COMPILING IMS COBOL SQL PROGRAM //********************************** //COBOL1 EXEC PGM=IGYCRCTL, // PARM='LIST,XREF,CP(37),SQLIMS("APOSTSQL"),DUMP,LIB,DYNAM' //STEPLIB DD DSN=IGYV5R10.TRIAL.SIGYCOMP,DISP=SHR // DD DSN=IGYV5R10.TRIAL.SIGYMAC,DISP=SHR // DD DSN=IMSBLD.IMSTS%%.CRESLIB,DISP=SHR //SYSLIB DD DSN=IGYV5R10.TRIAL.CEEZ1D0.SCEERUN,DISP=SHR // DD DSN=USER.PRIVATE.PROCLIB,DISP=SHR //SYSPRINT DD SYSOUT=A //SYSLIN DSN=&&LOADSET,DISP=(MOD,PASS), // DD UNIT=SYSDA,SPACE=(80,(500,200))
Considerations • The IMS catalog must be enabled and loaded with the database metadata needed by the COBOL SQL application. • Batch and DB Batch, CICS and DB2 SP are not supported for IMS COBOL SQL access. • Specify at least 12M for your IMS dependent region size for running a COBOL SQL application.
Considerations (cont’d) • SQL Keywords and API – Not all IMS JDBC SQL keywords are currently supported for COBOL applications. For example, aggregate functions and XML are not supported – COBOL SQL supports keywords used for database access calls only – SQL COMMIT and ROLLBACK keywords are not supported. Use IMS DB system services call to commit or roll back your database changes – Only one cursor and SQL statement can be active at a time – Only EBCDIC CCSID 37 and 1140 codepages for the COBOL CODEPAGE option are supported. – Always fully qualify all tables (segments) and columns (fields) in SQL statements. Specify the schema (PCB) name – e.g. PCB01.HOSPITAL • For IMS database services, GSAM, IMS TM and Message processing services, continue to use DL/I API
Documentation • Application Programming Guide – Programming for IMS -> Application programming -> Application Programming for SQL ! • Application Programming Reference – Programming for IMS -> Application programming APIs -> SQL programming reference ! • Messages and Codes – Troubleshooting for IMS -> IMS component codes -> SQL codes
Thank You Your feedback is important!
Standard Access to IMS Data Access from COBOL Using SQL Haley Fung, IBM firstname.lastname@example.org
IMS Database Access Enhancements ... SQL access to IMS Data ... • Customers need access to IMS databases using SQL from IMS managed COBOL application ...
The IMS Connect programming model provides access to information management systems (IMS) transactions using ... Maps the automation data types to COBOL ...
IBM IMS Technical Symposium ... Standard access to IMS data assets from COBOL using SQL ... Standard access to IMS data assets from COBOL using SQL
3 Gateway Features and Restrictions. ... supports SQL data access to IMS/DB data in the ... of executing updates on the IMS data source using the ...
Effected SMHA: Using IMS data for 2012 as a ... Standard Access to IMS Data - from COBOL using SQL. ... Direct Access to IMS Data - IMS Phoenix UG- June ...
... 5.0 Web applications to access applications and data on IBM systems running ... Using COMTI with IMS. ... is a standard feature of Microsoft® SQL ...
your sensitive data. Oracle Access Manager for CICS is ... Interface to Oracle servers using standard ANSI/ISO SQL ... Oracle Access Manager, Pro*COBOL ...
2 Gateway Features and Restrictions. ... SQL data access to IMS/DB data in the ... into the Oracle standard data dictionary format ...