Robert Hain, profile picture
Uploaded byRobert Hain
PDF, PPTX681 views

Ims11 ims13 application programming enhancements - IMS UG May 2014 Sydney & Melbourne

The document outlines the IMS 13 native SQL support for COBOL, enabling access to IMS databases using SQL from COBOL applications. It details the benefits of reducing development costs by leveraging existing SQL skills, the process for coding SQL statements within IMS COBOL applications, and the handling of SQL errors. Additionally, it describes the structure of SQL communications, supported SQL statements, and key terminology including host variables and SQLCA for error handling.

Download as PDF, PPTX
® IMS 13 IMS 13 Application Programming © 2013 IBM Corporation
IMS 13 IMS Native SQL Support for COBOL 07- IMS Application: 2
IMS 13 IMS Native SQL Support for COBOL Requirement – Customers need access to IMS databases using SQL from IMS managed COBOL application programs Solution – Enable SQL statements to be coded in an IMS COBOL application – Provide COBOL compiler co-processor function for IMS SQL statements – Convert SQL statements to DL/I database calls 07- IMS Application: 3 – Convert SQL statements to DL/I database calls • Handle SQL errors Benefits – Reduce application development cost by leveraging existing SQL skills – Expands IMS database access for application and database developers.
IMS 13 IMS Native SQL Support for COBOL solution The SQL support provided for the set of DLI data base access calls – Match SQL keywords supported by the IMS Universal JDBC driver – Support SQL keywords required by COBOL – Support both Static and Dynamic SQL IMS COBOL applications with embedded SQL statements are supported: – IMS TM/DB 07- IMS Application: 4 – IMS TM/DB • MPP, IFP, BMP – DBCTL BMP For IMS database services, GSAM, IMS TM and Message processing services – Continue to use DL/I API
IMS 13 To include IMS queries in an IMS COBOL application program Choose one of the following methods for communicating with IMS: – Static SQL – Embedded dynamic SQL Declare the tables (Segments) that you use. – DCLGEN can generate these declarations. Define an SQL communications area (SQLCA) – or declare SQLSTATE and SQLCODE host variables. Define at least one SQL descriptor area (SQLDA). 07- IMS Application: 5 Define at least one SQL descriptor area (SQLDA). Declare data items for passing data between IMS and a host language: – host variables – host variable arrays – host structures Code SQL statements to access IMS data. Check SQLCA to verify the execution of the SQL statements. Handle any SQL error codes returned by IMS.
IMS 13 Declaring table and view definitions DCLGEN generates a table or view declaration – SQL DECLARE TABLE statement – Corresponding COBOL record description IMS Catalog provides the information Example: WORKING-STORAGE SECTION. EXEC IMSSQL INCLUDE DFSIVP37.TELEPCB IMS Catalog Metadata //www.ibm.com/ims/PSB" psbName="DFSIVP37" 07- IMS Application: 6 INCLUDE DFSIVP37.TELEPCB END-EXEC. EXEC IMSSQL DECLARE DFSIVP37.TELEPCB (LNAME CHAR(10) FNAME CHAR(10), EXT CHAR(10), ZIP CHAR(7)) END-EXEC. //www.ibm.com/ims/PSB" psbName="DFSIVP37" <dbPCBname="TELEPCB" dbdName="DB2IVP" <senseg name="A1111111"></senseg> //www.ibm.com/ims/DBD" dbdName="DB2IVP" <segment imsName="A1111111" name="TELESEG" field imsDatatype="C" imsName="A1111111" name="LNAME" seqType="U"><startPos>1</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="FNAME"> <startPos>11</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="EXT"> <startPos>21</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="ZIP"> <startPos>31</startPos><bytes>7</bytes>
IMS 13 Declaring table and view definitions… DCLGEN generates a table or view declaration – SQL DECLARE TABLE statement – Corresponding COBOL record description IMS Catalog provides the information Example: IMS Catalog Metadata //www.ibm.com/ims/PSB" psbName="DFSIVP37"********************************* 07- IMS Application: 7 //www.ibm.com/ims/PSB" psbName="DFSIVP37" <dbPCBname="TELEPCB" dbdName="DB2IVP" <senseg name="A1111111"></senseg> //www.ibm.com/ims/DBD" dbdName="DB2IVP" <segment imsName="A1111111" name="TELESEG" field imsDatatype="C" imsName="A1111111" name="LNAME" seqType="U"><startPos>1</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="FNAME"> <startPos>11</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="EXT"> <startPos>21</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="ZIP"> <startPos>31</startPos><bytes>7</bytes> ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7).
IMS 13 IMS Native SQL Support for COBOL solution Compile IMS program using COBOL compiler with the IMSSQL option – Create an executable program to be run in IMS. – IMS co-processor knows when a particular SQL statement begins and ends by the following delimits for SQL statements: – EXEC IMSSQL SQL-STATEMENT 07- IMS Application: 8 SQL-STATEMENT – END-EXEC. SQLTDLI – non-language-specific interface added to DFSLI000
IMS 13 IMS COBOL SQL application compiled and linked IMS COBOL application source files with SQL statements COBOL Compiler with IMS coprocessor Translate EXEC SQLIMS 07- IMS Application: 9 Libraries Object files COBOL Link Executable Program EXEC SQLIMS INCLUDE DFSLI000
IMS 13 IMS SQL Call Request Handler IMS Native SQL Processor DFSLI000 SQLTDLI SQL SQLCA EXEC IMSSQL : : Retrieve IMS database PCB Schema metadata on first SQL call Parse and validate SQL IMS Catalog Metadata COBOL Application 64-Bit Storage 07- IMS Application: 10 IMS DB DLI Parse and validate SQL Build and make DLI call to access IMS data Perform aggregation on results data (if needed) Map results data back to the application SQLCA + Data IMS MPP,IFP,BMP
IMS 13 IMS Catalog metadata Contains IMS program resources, database resources, and relevant application metadata –Database Definitions – Segments – Fields IMS Metadata –Application program specifications Arrays and Structures Field data types and data type conversion Field redefines Alternative Field maps for a segment 07- IMS Application: 1111 ACBLIB ACBGEN DBDGEN/PSBGEN Extended PSB source data Extended DBD source data COBOL source IMS Explorer PSB/DBD source
IMS 13 Hierarchical to Relational Terminology Mapping 53SJ9 | Mary | 111 Penny Lane 53SJ8 | Bob | 240 Elm St. Dealer Segment 53SJ7 | George | 555 Bailey Ave. Hierarchical Design Relational Design DealerID DealerName DealerAddress Dealer Table 0 53SJ7 George 555 Bailey Ave. 1 53SJ8 Bob 240 Elm St. 2 53SJ9 Mary 111 Penny Ln. ... ... ... ... Row 1 - Row N - Segment 1 (Row 1) - 07- IMS Application: 12 UU45 | Dodge | Viper PR27 | Dodge | Durango Model Segment FF13 | Toyota | Camry ID Make Model Dealer Model Table UU45 Dodge Viper 53SJ7 0 PR27 Dodge Durango 53SJ7 0 FF13 Toyota Camry 53SJ7 0 JR27 Dodge Durango 53SJ8 1 WJ45 Mercury Cougar 53SJ8 1 ... ... ... ... ... JPR27 | Dodge | Durango WJ45 | Mercury | Cougar Row 1 - Row N - Note: Segment Names ~ Table Names Segment Instances ~ Table Rows Segment Field Names ~ Column Names Segment unique key ~ Table primary key IMS foreign key field ~ Table foreign key PCB ~ Schema
IMS 13 IMS SQL result set SELECT LastName, FirstName, ZipCode FROM DBPCB1.PHONEBOOK SQL SSA PHONESEG IOArea 07- IMS Application: 13 ResultSet1 ROW1 IOArea Lastname LAST1 FIRST1 555-1234 9999111 LAST1 FIRST1 9999111 GHU GHN LAST2 FIRST2 555-2234 9999222 ResultSet2 LAST2 ROW2 FIRST2 9999222 Firsname Extension Zipcode
IMS 13 IMS Native SQL Support for COBOL solution Errors and warnings conditions of the IMS SQL executions are set by IMS using the SQL Communication Area (SQLCA). The IMS COBOL applications can use the SQLCA or define a stand-alone SQLCODE or SQLSTATE host variables to check the result of an SQL execution. – SQLCODE is a return code for a successful or failed SQL operation. – SQLERRM contains a detail error message – SQLWARN represents the warning 07- IMS Application: 14 – SQLWARN represents the warning – SQLSTATE represents the common codes for common error conditions
IMS 13 Restrictions Subset of SQL 2008 is supported No XML DB support at this time CICS managed application is not supported DB2 Stored Procedures runtime environment is not supported DL/I Batch runtime is not supported 07- IMS Application: 15 DL/I Batch runtime is not supported
IMS 13 Appendix 07- IMS Application: 16
IMS 13 IMS Native SQL Support for COBOL Terminology Host variables – Fields defined in a program used both to send and receive data processed by IMS – In COBOL, host variables are defined in the working-storage area and the storage is owned by the program itself. SQL communication area (SQLCA) 07- IMS Application: 17 SQL communication area (SQLCA) – 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 SQLCA. The main elements in the SQLCA are: – SQLCODE – A return code represents a successful or failed SQL operation – SQLSTATE – Common codes for common error conditions which conform to the SQL standard.
IMS 13 IMS Native SQL supported Statements SQL data manipulation statements – SELECT… FROM… to retrieve data – INSERT INTO… VALUES… to insert data – UPDATE… SET… to update data – DELETE FROM… to delete data 07- IMS Application: 18 – WHERE… AND… OR… to perform conditional selection of data – COUNT, AVG, MAX, MIN, SUM to perform data aggregation functions – ALL, AS, DISTINCT to get specific set of data or name data columns with specific names – GROUP BY, ORDER BY, ASC, DESC to sort and group result data – [INNER] JOIN...ON, – FETCH FIRST n ROW(s) ONLY to fetch the first numbers of rows
IMS 13 SQL keywords required by COBOL for SQL execution Pre-compiler directives – DECLARE CURSOR, DECLARE STATEMENT, DECLARE TABLE – INCLUDE – WHENEVER Cursor processing statements – OPEN CURSOR 07- IMS Application: 19 – OPEN CURSOR – CLOSE CURSOR SQL statement execution – PREPARE – EXECUTE IMMEDIATE – EXECUTE and PREPARE – FETCH… INTO… – COMMIT and ROLLBACK for recovery and restart
IMS 13 IMS Defining the SQL communications area (SQLCA) WORKING-STORAGE SECTION. ***************************************************** * SQL INCLUDE FOR SQLCA ***************************************************** EXEC IMSSQL INCLUDE SQLCA 07- IMS Application: 20 END-EXEC.
IMS 13 SQLCA 01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S9(9) COMP-5. 05 SQLCODE PIC S9(9) COMP-5. 05 SQLERRM. 49 SQLERRML PIC S9(4) COMP-5. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD PIC s9(9) comp-5 OCCURS 6 TIMES. 05 SQLWARN. 07- IMS Application: 21 05 SQLWARN. 10 SQLWARN0 PIC X. 10 SQLWARN1 PIC X. 10 SQLWARN2 PIC X. 10 SQLWARN3 PIC X. 10 SQLWARN4 PIC X. 10 SQLWARN5 PIC X. 10 SQLWARN6 PIC X. 10 SQLWARN7 PIC X. 05 SQLEXT. 10 SQLWARN8 PIC X. 10 SQLWARN9 PIC X. 10 SQLWARNA PIC X. 10 SQLSTATE PIC X(5).
IMS 13 IMS Native SQL Support for COBOL solution The SQLCODE is a return code for a successful or failed SQL operation. – If SQLCODE = 0 and SQLWARN0 is blank, execution was successful. – If SQLCODE = 100, "no data" was found. – If SQLCODE > 0 and not = 100, execution was successful with a warning. – If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful with a warning. – If SQLCODE < 0, execution was not successful. 07- IMS Application: 22 – If SQLCODE < 0, execution was not successful. IMS DL/I Status Codes corresponding SQLCODE
IMS 13 IMS Native SQL Support for COBOL solution When IMS processes an SQL statement, – return codes indicate the success or failure in SQLCODE and SQLSTATE. When IMS processes a successful FETCH statement – SQLERRD(3) in the SQLCA is set to the number of returned rows. When IMS processes a multiple-row FETCH statement, – SQLCODE is set to +100 if the last row in the table has been returned 07- IMS Application: 23 – SQLCODE is set to +100 if the last row in the table has been returned When IMS processes an UPDATE, INSERT, or DELETE statement – SQLERRD(3) in the SQLCA is set to the number of rows that are updated, inserted, or deleted. When IMS processes a TRUNCATE statement – SQLERRD(3) in the SQLCA is set to -1 – The number of rows that are deleted is not returned.
IMS 13 Messages and Codes Abend 3062 – Error loading IMS modules SQLCODE -904/SQLSTATE 57013 – IMS catalog has not been started SQLCODE -204/SQLSTATE 42704 07- IMS Application: 24 SQLCODE -204/SQLSTATE 42704 – PCB name specified in the SQL statement is not valid. SQLCODE -5001/SQLSTATE 42703 – Segment name specified in the SQL statement is not valid. SQLCODE -158/SQLSTATE 42811 – The number of output fields specified in the SQL statement and the number of host variables to be fetched in the FETCH statement is not the same.
IMS 13 Defining SQL descriptor areas (SQLDA) SQLDA stores information about prepared SQL statements or host variables. can be read by either the application program or IMS Required for following SQL statements – DESCRIBE statement-name INTO descriptor-name 07- IMS Application: 25 – DESCRIBE CURSOR host-variable INTO descriptor-name – DESCRIBE INPUT statement-name INTO descriptor-name – DESCRIBE TABLE host-variable INTO descriptor-name – EXECUTE … USING DESCRIPTOR descriptor-name – FETCH … INTO DESCRIPTOR descriptor-name – OPEN … USING DESCRIPTOR descriptor-name – PREPARE … INTO descriptor-name
IMS 13 IMS Native SQL Terminology The set of SQL statements can be classified into three main categories: – Data Definition Language (DDL) to manage and define database – Data Manipulation Language (DML) to access and manipulate data • SQL support provided is only for data manipulation – Data Control Language (DCL) to control data access Static SQL statements – segment and field names associated with the SQL calls are defined in the 07- IMS Application: 26 – segment and field names associated with the SQL calls are defined in the program source – pre-processed when the program is being pre-compiled • Parsed, validated and converted into an executable form Dynamic SQL statements – statement is constructed and prepared at run time – segment and field names associated with the SQL calls are not known at pre-compile time – parsed and validated by the database at runtime each time the SQL statement is executed.
IMS 13 IMS COBOL Host Structure Variables and Static SELECT SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 27 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). MOVE ‘LAST1’ to LNAME. EXEC IMSSQL SELECT LNAME, FNAME, ZIP INTO :LNAME,:FTNAME,:ZIP FROM DFSIVP37.TELEPCB WHERE LNAME = :LNAME END-EXEC.
IMS 13 IMS COBOL Host Structure Variables and Static INSERT SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 28 EXEC IMSSQL INSERT INTO DFSIVP37.TELEPCB (LNAME, FNAME,EXT,ZIP) VALUES (’ALAST’, ’BFIRST’, ’555-5555’,’9999999’) END-EXEC. 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7).
IMS 13 IMS COBOL Host Structure Variables and Static UPDATE SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 29 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). EXEC IMSSQL UPDATE DFSIVP37.TELEPCB SET FNAME = ‘FIRSTB’ WHERE LNAME=’ALAST’ END-EXEC.
IMS 13 IMS COBOL Host Structure Variables and Static DELETE SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 30 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). EXEC IMSSQL DELETE FROM DFSIVP37.TELEPCB WHERE LNAME=’ALAST’ END-EXEC.
IMS 13 Difference between Static and Dynamic SQL Flexibility of static SQL with host variables – When you use static SQL, you cannot change the form of SQL statements unless you make changes to the program. However, you can increase the flexibility of static statements by using host variables. Example: In the following example, the UPDATE statement can update an employee. at run time determine employee to be updated. 01 DCLTELEPCB. 07- IMS Application: 31 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). Get FNAME and LNAME input values EXEC IMSSQL UPDATE DFSIVP37.TELEPCB SET FNAME = FNAME INPUT value WHERE LNAME= LNAME INPUT value END-EXEC.
IMS 13 IMS COBOL Dynamic SQL sample Dynamic SQL processing – A program that provides for dynamic SQL accepts as input, or generates, an SQL statement in the form of a character string WORKING-STORAGE SECTION. 05 SELECT-STATEMENT. 49 SELECT-STATEMENT-LEN PIC S9(4) COMP VALUE +39. 49 SELECT-STATEMENT-TXT PIC X(32). 07- IMS Application: 32 49 SELECT-STATEMENT-TXT PIC X(32). PROCEDURE DIVISION. EXEC IMSSQL DECLARE TELE1 CURSOR FOR DYSQL END-EXEC. EXEC IMSSQL WHENEVER SQLERROR GOTO 100-DBERROR END-EXEC.
IMS 13 IMS COBOL Dynamic SQL sample.. MOVE "SELECT LNAME,FNAME,ZIP FROM DFSIVP37.TELEPCB" TO SELECT- STATEMENT-TXT. EXEC IMSSQL PREPARE DYSQL FROM :SELECT-STATEMENT END-EXEC. EXEC IMSSQL 07- IMS Application: 33 OPEN TELE1 END-EXEC. EXEC IMSSQL FETCH TELE1 INTO :PHONEBOOK END-EXEC. EXEC IMSSQL CLOSE TELE1 END-EXEC.
IMS 13 Solution Highlights SQL support for COBOL – Offer SQL as a query language for COBOL programs to access IMS database – EXEC IMSSQL as the interface to execute SQL calls to IMS databases – IMS co-processor in the COBOL Compiler 07- IMS Application: 34 – IMS co-processor in the COBOL Compiler – SQL calls are validated semantically and translated at runtime • No bind process at deployment time
IMS 13 IMS Universal Driver Enhancements 07- IMS Application: 35
IMS 13 Support for Native SQL Processor IMS Universal Drivers use IMS common SQL processing SQL conversion is moved from the client to IMS Migration – IMS Universal Drivers built-in migration support Benefit 07- IMS Application: 36 Benefit – provides consistent SQL processing for all SQL calls – reduces the amount of processing on the client side and the amount of network traffic for data aggregation
IMS 13 Support for native SQL Native SQL Processor IMS 13 IMS JDBC Driver ODBM IMS Connect Client Application DRDADRDA MPP,IFP,BMP Regions COBOL Native SQL 07- IMS Application: 37 RYO IMS .NET IMS DB IMS Catalog IMS JDBC Driver Java Enabled Regions Type 4 Type 2 IMS DB IMS Catalog SQL SQL IMS Native SQL Processor DLI z/OS SQL
IMS 13 DBDGEN Support for Unsigned Data Types APAR - PM64418 PTF - UK79443 Problem – IMS Explorer generates DBD source which includes new unsigned data types and internal type converters which are not supported by DBDGEN – Large positive signed 2's complement data element values are made negative. 07- IMS Application: 38 negative. Solution – FIELD macro add support UBYTE, UINT, ULONG, and USHORT data type values on the DATATYPE= parameter – DFSMARSH macro add support UBYTE, UINT, ULONG, and USHORT internal type converter values on the INTERNALTYPECONVERTER= parameter
IMS 13 DBDGEN FIELD Statement >>-FIELD >-DATATYPE= +-ARRAY +-BINARY +-BIT +-BYTE +-UBYTE +-CHAR +-DATE 07- IMS Application: 39 +-DECIMAL(pp,ss) +-DOUBLE +-FLOAT +-INT +-UINT +-LONG +-ULONG +-OTHER +-SHORT +-USHORT +-STRUCT +-TIME +-TIMESTAMP +-XML
IMS 13 DBDGEN DFSMARSH Statement >>-DFSMARSH >--+-,INTERNALTYPECONVERTER= +-CHAR +-BIT +-BINARY 07- IMS Application: 40 +-BYTE +-UBYTE +-SHORT +-USHORT +-INT +-UINT +-LONG +-ULONG +-FLOAT +-DOUBLE +-PACKEDDECIMAL +-ZONEDDECIMAL +-CLOB +-BLOB +-XML_CLOB +-ARRAY +-STRUCT
IMS 13 IMS Universal Drivers Unsigned Type Support APAR - PM62076 PTF - UK80831 Problem – NullPointerException in the IMS Universal Drivers when accessing a Secondary Index with a single source field that is defined as packed decimal. Solution The IMS Universal Drivers have added support for the following data types: – UTINYINT 07- IMS Application: 41 – UTINYINT – USMALLINT – UINTEGER – UBIGINT Benefits – Users are now able to access a Secondary Index with a single source field of any type – Users will no longer receive an UnsupportedTypeConversion message when accessing a variable length segment where the length field (LL) is defined as BINARY.
IMS 13 COBOL data formats mapped to IMS Java data constants PIC X(25) CHAR PIC 9(02) COMP UTINYINT PIC S9(04) COMP SMALLINT PIC 9(04) COMP USMALLINT PIC S9(06) COMP-4 INTEGER PIC 9(06) COMP-4 UINTEGER 07- IMS Application: 42 PIC 9(06) COMP-4 UINTEGER PIC 9(06)V99 COMP or COMP-4 BINARY PIC S9(12) BINARY BIGINT PIC 9(12) BINARY UBIGINT COMP-1 FLOAT COMP-2 DOUBLE PIC S9(06)V99 ZONEDDECIMAL PIC 9(06).99 ZONEDDECIMAL PIC S9(06)V99 COMP-3 PACKEDDECIMAL

More Related Content

PPT
DB2 UDB for z/OS Version 7 - An Overview
byCraig Mullins
 
PPTX
Puppet Camp Boston 2014: Orchestrating Infrastructure Change Using Puppet Rak...
byPuppet
 
PDF
Puppet Camp Boston 2014: Orchestrating Infrastructure Change Using Puppet Rak...
byPuppet
 
PPT
Les14[1]Controlling User Access
bysiavosh kaviani
 
PDF
Scale Up Performance with Intel® Development
byIntel IT Center
 
PDF
Achieving Lowest Latencies at Highest Message Rates: Solarflare & Intel webcast
byfinteligent
 
PDF
Programar En Fortran
bySaul Bernal
 
PPT
SQL / PL
bysrijanani2030
 
DB2 UDB for z/OS Version 7 - An Overview
byCraig Mullins
 
Puppet Camp Boston 2014: Orchestrating Infrastructure Change Using Puppet Rak...
byPuppet
 
Puppet Camp Boston 2014: Orchestrating Infrastructure Change Using Puppet Rak...
byPuppet
 
Les14[1]Controlling User Access
bysiavosh kaviani
 
Scale Up Performance with Intel® Development
byIntel IT Center
 
Achieving Lowest Latencies at Highest Message Rates: Solarflare & Intel webcast
byfinteligent
 
Programar En Fortran
bySaul Bernal
 
SQL / PL
bysrijanani2030
 

Similar to Ims11 ims13 application programming enhancements - IMS UG May 2014 Sydney & Melbourne

PPT
Creating Web Applications with IDMS, COBOL and ADSO
byMargaret Sliming
 
PDF
SMP / Extended orientation for IBM mainframes
bytejaswinireddy524612
 
PPT
Creating Web Applications with IDMS, COBOL and ADSO
byMargaret Sliming
 
PDF
Database design i_-_1_dl300
byKOI Lastone
 
PPTX
What's new in DB2 for i - IBM i 7.3 TR1 and IBM i 7.2 TR5 - final.pptx
byCesarDuarteMoreno
 
PPT
Application_Performance_V1
byMohan Reddy Timmapuram
 
Creating Web Applications with IDMS, COBOL and ADSO
byMargaret Sliming
 
SMP / Extended orientation for IBM mainframes
bytejaswinireddy524612
 
Creating Web Applications with IDMS, COBOL and ADSO
byMargaret Sliming
 
Database design i_-_1_dl300
byKOI Lastone
 
What's new in DB2 for i - IBM i 7.3 TR1 and IBM i 7.2 TR5 - final.pptx
byCesarDuarteMoreno
 
Application_Performance_V1
byMohan Reddy Timmapuram
 

More from Robert Hain

PDF
IMS13 mix it up
byRobert Hain
 
PDF
IMS12 ims performance tools
byRobert Hain
 
PDF
IMS11 BMC Susbystem Optimizer - subzero
byRobert Hain
 
PDF
IMS10 unleash the capabilities of new technologies
byRobert Hain
 
PDF
IMS09 ims v14 higlights
byRobert Hain
 
PDF
IMS08 the momentum driving the ims future
byRobert Hain
 
PDF
IMS06 operational management with big data tools
byRobert Hain
 
PDF
IMS05 IMS V14 8gb osam for haldb
byRobert Hain
 
PDF
IMS04 BMC Software Strategy and Roadmap
byRobert Hain
 
PDF
IMS03 how design thinking is shaping ims
byRobert Hain
 
PDF
IMS02 autonomics for ims with the ibm management console for ims and db2 fo...
byRobert Hain
 
PDF
IMS01 IMS Keynote
byRobert Hain
 
PDF
Ims13 ims tools ims v13 migration workshop - IMS UG May 2014 Sydney & Melbo...
byRobert Hain
 
PDF
Ims12 workbench data visualization - IMS UG May 2014 Sydney & Melbourne
byRobert Hain
 
PDF
Ims09 ims in a sysplex environment - challanges and solutions - IMS UG May ...
byRobert Hain
 
PDF
Ims08 ims system administration - a different view - IMS UG May 2014 Sydney...
byRobert Hain
 
PDF
Ims06 change you can believe in
byRobert Hain
 
PDF
Ims05 ims 100 k benchmark
byRobert Hain
 
PDF
Ims04 ims modernization and integration - IMS UG May 2014 Sydney & Melbourne
byRobert Hain
 
PDF
Ims03 ims connect-monitoring and diagnostics - IMS UG May 2014 Sydney & Mel...
byRobert Hain
 
IMS13 mix it up
byRobert Hain
 
IMS12 ims performance tools
byRobert Hain
 
IMS11 BMC Susbystem Optimizer - subzero
byRobert Hain
 
IMS10 unleash the capabilities of new technologies
byRobert Hain
 
IMS09 ims v14 higlights
byRobert Hain
 
IMS08 the momentum driving the ims future
byRobert Hain
 
IMS06 operational management with big data tools
byRobert Hain
 
IMS05 IMS V14 8gb osam for haldb
byRobert Hain
 
IMS04 BMC Software Strategy and Roadmap
byRobert Hain
 
IMS03 how design thinking is shaping ims
byRobert Hain
 
IMS02 autonomics for ims with the ibm management console for ims and db2 fo...
byRobert Hain
 
IMS01 IMS Keynote
byRobert Hain
 
Ims13 ims tools ims v13 migration workshop - IMS UG May 2014 Sydney & Melbo...
byRobert Hain
 
Ims12 workbench data visualization - IMS UG May 2014 Sydney & Melbourne
byRobert Hain
 
Ims09 ims in a sysplex environment - challanges and solutions - IMS UG May ...
byRobert Hain
 
Ims08 ims system administration - a different view - IMS UG May 2014 Sydney...
byRobert Hain
 
Ims06 change you can believe in
byRobert Hain
 
Ims05 ims 100 k benchmark
byRobert Hain
 
Ims04 ims modernization and integration - IMS UG May 2014 Sydney & Melbourne
byRobert Hain
 
Ims03 ims connect-monitoring and diagnostics - IMS UG May 2014 Sydney & Mel...
byRobert Hain
 

Recently uploaded

PDF
The Role of Human Experiences (HX) in GenAI Adoption
byDr. Tathagat Varma
 
PDF
TrustArc Webinar - The Future of Third-Party Privacy Risk: Trends, Tactics & ...
byTrustArc
 
PDF
Fairness and Bias in AI Ethics and Explainability
byShiwani Gupta
 
PDF
Master Deck: GraphSummit Bengaluru (Oct 7)
byNeo4j
 
PDF
NeuroXR: Current Research and Opportunities
byMark Billinghurst
 
PDF
How to get started with Agentic Automation
byUiPathCommunity
 
PDF
Artificial Intelligence and YOU - Thinking Thoughtfully about AI
byMicah Melling
 
PDF
CIDT: Blockchain, DeFi & Product Engineering - MVPs in 4 Weeks with a Senior-...
byTetianaBuchynska1
 
PDF
Session 2 - Agentic Orchestration with UiPath Maestro
byDianaGray10
 
PPTX
⚡Level Up Your Slack Game: Workflows & Conditional Branching🎉
bySanjeetMishra29
 
PDF
Session 3 - Specialized AI Associate Series: AI Powered Automation through sp...
byDianaGray10
 
PDF
A Guide to Microsoft Azure's Scalable and Secure Cloud Solutions
byTeleglobal International
 
PPTX
sap 2016-09-14_PP_MRPType_VB_Blogged.pptx
byAmira Jemi
 
PDF
UiPath DevConnect 2025: Introduction to Agentic Automation
byDianaGray10
 
PDF
Meta and Apple close to settling EU cases.pdf
byLUMINATIVE MEDIA/PROJECT COUNSEL MEDIA GROUP
 
PDF
AGV PROTOCOL BRAND KIT COMPLETE VIEW.pdf
byfagbolade
 
PDF
A SEA of Energy Efficiency Opportunities!
byMemoori
 
PDF
From Infrastructure to Insight: Technical Pathways to Value in Europe’s Compu...
byMindtrek
 
PDF
Manage VMs on Kubernetes - Introduction to KubeVirt - Meetup MUC 10.2025
byTobias Schneck
 
PDF
Atlantix is an AI-first venture studio, building companies with AI at the core
byadmin625551
 
The Role of Human Experiences (HX) in GenAI Adoption
byDr. Tathagat Varma
 
TrustArc Webinar - The Future of Third-Party Privacy Risk: Trends, Tactics & ...
byTrustArc
 
Fairness and Bias in AI Ethics and Explainability
byShiwani Gupta
 
Master Deck: GraphSummit Bengaluru (Oct 7)
byNeo4j
 
NeuroXR: Current Research and Opportunities
byMark Billinghurst
 
How to get started with Agentic Automation
byUiPathCommunity
 
Artificial Intelligence and YOU - Thinking Thoughtfully about AI
byMicah Melling
 
CIDT: Blockchain, DeFi & Product Engineering - MVPs in 4 Weeks with a Senior-...
byTetianaBuchynska1
 
Session 2 - Agentic Orchestration with UiPath Maestro
byDianaGray10
 
⚡Level Up Your Slack Game: Workflows & Conditional Branching🎉
bySanjeetMishra29
 
Session 3 - Specialized AI Associate Series: AI Powered Automation through sp...
byDianaGray10
 
A Guide to Microsoft Azure's Scalable and Secure Cloud Solutions
byTeleglobal International
 
sap 2016-09-14_PP_MRPType_VB_Blogged.pptx
byAmira Jemi
 
UiPath DevConnect 2025: Introduction to Agentic Automation
byDianaGray10
 
Meta and Apple close to settling EU cases.pdf
byLUMINATIVE MEDIA/PROJECT COUNSEL MEDIA GROUP
 
AGV PROTOCOL BRAND KIT COMPLETE VIEW.pdf
byfagbolade
 
A SEA of Energy Efficiency Opportunities!
byMemoori
 
From Infrastructure to Insight: Technical Pathways to Value in Europe’s Compu...
byMindtrek
 
Manage VMs on Kubernetes - Introduction to KubeVirt - Meetup MUC 10.2025
byTobias Schneck
 
Atlantix is an AI-first venture studio, building companies with AI at the core
byadmin625551
 

Ims11 ims13 application programming enhancements - IMS UG May 2014 Sydney & Melbourne

  • 1.
    ® IMS 13 IMS 13Application Programming © 2013 IBM Corporation
  • 2.
    IMS 13 IMS NativeSQL Support for COBOL 07- IMS Application: 2
  • 3.
    IMS 13 IMS NativeSQL Support for COBOL Requirement – Customers need access to IMS databases using SQL from IMS managed COBOL application programs Solution – Enable SQL statements to be coded in an IMS COBOL application – Provide COBOL compiler co-processor function for IMS SQL statements – Convert SQL statements to DL/I database calls 07- IMS Application: 3 – Convert SQL statements to DL/I database calls • Handle SQL errors Benefits – Reduce application development cost by leveraging existing SQL skills – Expands IMS database access for application and database developers.
  • 4.
    IMS 13 IMS NativeSQL Support for COBOL solution The SQL support provided for the set of DLI data base access calls – Match SQL keywords supported by the IMS Universal JDBC driver – Support SQL keywords required by COBOL – Support both Static and Dynamic SQL IMS COBOL applications with embedded SQL statements are supported: – IMS TM/DB 07- IMS Application: 4 – IMS TM/DB • MPP, IFP, BMP – DBCTL BMP For IMS database services, GSAM, IMS TM and Message processing services – Continue to use DL/I API
  • 5.
    IMS 13 To includeIMS queries in an IMS COBOL application program Choose one of the following methods for communicating with IMS: – Static SQL – Embedded dynamic SQL Declare the tables (Segments) that you use. – DCLGEN can generate these declarations. Define an SQL communications area (SQLCA) – or declare SQLSTATE and SQLCODE host variables. Define at least one SQL descriptor area (SQLDA). 07- IMS Application: 5 Define at least one SQL descriptor area (SQLDA). Declare data items for passing data between IMS and a host language: – host variables – host variable arrays – host structures Code SQL statements to access IMS data. Check SQLCA to verify the execution of the SQL statements. Handle any SQL error codes returned by IMS.
  • 6.
    IMS 13 Declaring tableand view definitions DCLGEN generates a table or view declaration – SQL DECLARE TABLE statement – Corresponding COBOL record description IMS Catalog provides the information Example: WORKING-STORAGE SECTION. EXEC IMSSQL INCLUDE DFSIVP37.TELEPCB IMS Catalog Metadata //www.ibm.com/ims/PSB" psbName="DFSIVP37" 07- IMS Application: 6 INCLUDE DFSIVP37.TELEPCB END-EXEC. EXEC IMSSQL DECLARE DFSIVP37.TELEPCB (LNAME CHAR(10) FNAME CHAR(10), EXT CHAR(10), ZIP CHAR(7)) END-EXEC. //www.ibm.com/ims/PSB" psbName="DFSIVP37" <dbPCBname="TELEPCB" dbdName="DB2IVP" <senseg name="A1111111"></senseg> //www.ibm.com/ims/DBD" dbdName="DB2IVP" <segment imsName="A1111111" name="TELESEG" field imsDatatype="C" imsName="A1111111" name="LNAME" seqType="U"><startPos>1</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="FNAME"> <startPos>11</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="EXT"> <startPos>21</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="ZIP"> <startPos>31</startPos><bytes>7</bytes>
  • 7.
    IMS 13 Declaring tableand view definitions… DCLGEN generates a table or view declaration – SQL DECLARE TABLE statement – Corresponding COBOL record description IMS Catalog provides the information Example: IMS Catalog Metadata //www.ibm.com/ims/PSB" psbName="DFSIVP37"********************************* 07- IMS Application: 7 //www.ibm.com/ims/PSB" psbName="DFSIVP37" <dbPCBname="TELEPCB" dbdName="DB2IVP" <senseg name="A1111111"></senseg> //www.ibm.com/ims/DBD" dbdName="DB2IVP" <segment imsName="A1111111" name="TELESEG" field imsDatatype="C" imsName="A1111111" name="LNAME" seqType="U"><startPos>1</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="FNAME"> <startPos>11</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="EXT"> <startPos>21</startPos><bytes>10</bytes> field imsDatatype="C" imsName=" " name="ZIP"> <startPos>31</startPos><bytes>7</bytes> ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7).
  • 8.
    IMS 13 IMS NativeSQL Support for COBOL solution Compile IMS program using COBOL compiler with the IMSSQL option – Create an executable program to be run in IMS. – IMS co-processor knows when a particular SQL statement begins and ends by the following delimits for SQL statements: – EXEC IMSSQL SQL-STATEMENT 07- IMS Application: 8 SQL-STATEMENT – END-EXEC. SQLTDLI – non-language-specific interface added to DFSLI000
  • 9.
    IMS 13 IMS COBOLSQL application compiled and linked IMS COBOL application source files with SQL statements COBOL Compiler with IMS coprocessor Translate EXEC SQLIMS 07- IMS Application: 9 Libraries Object files COBOL Link Executable Program EXEC SQLIMS INCLUDE DFSLI000
  • 10.
    IMS 13 IMS SQLCall Request Handler IMS Native SQL Processor DFSLI000 SQLTDLI SQL SQLCA EXEC IMSSQL : : Retrieve IMS database PCB Schema metadata on first SQL call Parse and validate SQL IMS Catalog Metadata COBOL Application 64-Bit Storage 07- IMS Application: 10 IMS DB DLI Parse and validate SQL Build and make DLI call to access IMS data Perform aggregation on results data (if needed) Map results data back to the application SQLCA + Data IMS MPP,IFP,BMP
  • 11.
    IMS 13 IMS Catalogmetadata Contains IMS program resources, database resources, and relevant application metadata –Database Definitions – Segments – Fields IMS Metadata –Application program specifications Arrays and Structures Field data types and data type conversion Field redefines Alternative Field maps for a segment 07- IMS Application: 1111 ACBLIB ACBGEN DBDGEN/PSBGEN Extended PSB source data Extended DBD source data COBOL source IMS Explorer PSB/DBD source
  • 12.
    IMS 13 Hierarchical toRelational Terminology Mapping 53SJ9 | Mary | 111 Penny Lane 53SJ8 | Bob | 240 Elm St. Dealer Segment 53SJ7 | George | 555 Bailey Ave. Hierarchical Design Relational Design DealerID DealerName DealerAddress Dealer Table 0 53SJ7 George 555 Bailey Ave. 1 53SJ8 Bob 240 Elm St. 2 53SJ9 Mary 111 Penny Ln. ... ... ... ... Row 1 - Row N - Segment 1 (Row 1) - 07- IMS Application: 12 UU45 | Dodge | Viper PR27 | Dodge | Durango Model Segment FF13 | Toyota | Camry ID Make Model Dealer Model Table UU45 Dodge Viper 53SJ7 0 PR27 Dodge Durango 53SJ7 0 FF13 Toyota Camry 53SJ7 0 JR27 Dodge Durango 53SJ8 1 WJ45 Mercury Cougar 53SJ8 1 ... ... ... ... ... JPR27 | Dodge | Durango WJ45 | Mercury | Cougar Row 1 - Row N - Note: Segment Names ~ Table Names Segment Instances ~ Table Rows Segment Field Names ~ Column Names Segment unique key ~ Table primary key IMS foreign key field ~ Table foreign key PCB ~ Schema
  • 13.
    IMS 13 IMS SQLresult set SELECT LastName, FirstName, ZipCode FROM DBPCB1.PHONEBOOK SQL SSA PHONESEG IOArea 07- IMS Application: 13 ResultSet1 ROW1 IOArea Lastname LAST1 FIRST1 555-1234 9999111 LAST1 FIRST1 9999111 GHU GHN LAST2 FIRST2 555-2234 9999222 ResultSet2 LAST2 ROW2 FIRST2 9999222 Firsname Extension Zipcode
  • 14.
    IMS 13 IMS NativeSQL Support for COBOL solution Errors and warnings conditions of the IMS SQL executions are set by IMS using the SQL Communication Area (SQLCA). The IMS COBOL applications can use the SQLCA or define a stand-alone SQLCODE or SQLSTATE host variables to check the result of an SQL execution. – SQLCODE is a return code for a successful or failed SQL operation. – SQLERRM contains a detail error message – SQLWARN represents the warning 07- IMS Application: 14 – SQLWARN represents the warning – SQLSTATE represents the common codes for common error conditions
  • 15.
    IMS 13 Restrictions Subset ofSQL 2008 is supported No XML DB support at this time CICS managed application is not supported DB2 Stored Procedures runtime environment is not supported DL/I Batch runtime is not supported 07- IMS Application: 15 DL/I Batch runtime is not supported
  • 16.
    IMS 13 Appendix 07- IMSApplication: 16
  • 17.
    IMS 13 IMS NativeSQL Support for COBOL Terminology Host variables – Fields defined in a program used both to send and receive data processed by IMS – In COBOL, host variables are defined in the working-storage area and the storage is owned by the program itself. SQL communication area (SQLCA) 07- IMS Application: 17 SQL communication area (SQLCA) – 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 SQLCA. The main elements in the SQLCA are: – SQLCODE – A return code represents a successful or failed SQL operation – SQLSTATE – Common codes for common error conditions which conform to the SQL standard.
  • 18.
    IMS 13 IMS NativeSQL supported Statements SQL data manipulation statements – SELECT… FROM… to retrieve data – INSERT INTO… VALUES… to insert data – UPDATE… SET… to update data – DELETE FROM… to delete data 07- IMS Application: 18 – WHERE… AND… OR… to perform conditional selection of data – COUNT, AVG, MAX, MIN, SUM to perform data aggregation functions – ALL, AS, DISTINCT to get specific set of data or name data columns with specific names – GROUP BY, ORDER BY, ASC, DESC to sort and group result data – [INNER] JOIN...ON, – FETCH FIRST n ROW(s) ONLY to fetch the first numbers of rows
  • 19.
    IMS 13 SQL keywordsrequired by COBOL for SQL execution Pre-compiler directives – DECLARE CURSOR, DECLARE STATEMENT, DECLARE TABLE – INCLUDE – WHENEVER Cursor processing statements – OPEN CURSOR 07- IMS Application: 19 – OPEN CURSOR – CLOSE CURSOR SQL statement execution – PREPARE – EXECUTE IMMEDIATE – EXECUTE and PREPARE – FETCH… INTO… – COMMIT and ROLLBACK for recovery and restart
  • 20.
    IMS 13 IMS Definingthe SQL communications area (SQLCA) WORKING-STORAGE SECTION. ***************************************************** * SQL INCLUDE FOR SQLCA ***************************************************** EXEC IMSSQL INCLUDE SQLCA 07- IMS Application: 20 END-EXEC.
  • 21.
    IMS 13 SQLCA 01 SQLCA. 05SQLCAID PIC X(8). 05 SQLCABC PIC S9(9) COMP-5. 05 SQLCODE PIC S9(9) COMP-5. 05 SQLERRM. 49 SQLERRML PIC S9(4) COMP-5. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD PIC s9(9) comp-5 OCCURS 6 TIMES. 05 SQLWARN. 07- IMS Application: 21 05 SQLWARN. 10 SQLWARN0 PIC X. 10 SQLWARN1 PIC X. 10 SQLWARN2 PIC X. 10 SQLWARN3 PIC X. 10 SQLWARN4 PIC X. 10 SQLWARN5 PIC X. 10 SQLWARN6 PIC X. 10 SQLWARN7 PIC X. 05 SQLEXT. 10 SQLWARN8 PIC X. 10 SQLWARN9 PIC X. 10 SQLWARNA PIC X. 10 SQLSTATE PIC X(5).
  • 22.
    IMS 13 IMS NativeSQL Support for COBOL solution The SQLCODE is a return code for a successful or failed SQL operation. – If SQLCODE = 0 and SQLWARN0 is blank, execution was successful. – If SQLCODE = 100, "no data" was found. – If SQLCODE > 0 and not = 100, execution was successful with a warning. – If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful with a warning. – If SQLCODE < 0, execution was not successful. 07- IMS Application: 22 – If SQLCODE < 0, execution was not successful. IMS DL/I Status Codes corresponding SQLCODE
  • 23.
    IMS 13 IMS NativeSQL Support for COBOL solution When IMS processes an SQL statement, – return codes indicate the success or failure in SQLCODE and SQLSTATE. When IMS processes a successful FETCH statement – SQLERRD(3) in the SQLCA is set to the number of returned rows. When IMS processes a multiple-row FETCH statement, – SQLCODE is set to +100 if the last row in the table has been returned 07- IMS Application: 23 – SQLCODE is set to +100 if the last row in the table has been returned When IMS processes an UPDATE, INSERT, or DELETE statement – SQLERRD(3) in the SQLCA is set to the number of rows that are updated, inserted, or deleted. When IMS processes a TRUNCATE statement – SQLERRD(3) in the SQLCA is set to -1 – The number of rows that are deleted is not returned.
  • 24.
    IMS 13 Messages andCodes Abend 3062 – Error loading IMS modules SQLCODE -904/SQLSTATE 57013 – IMS catalog has not been started SQLCODE -204/SQLSTATE 42704 07- IMS Application: 24 SQLCODE -204/SQLSTATE 42704 – PCB name specified in the SQL statement is not valid. SQLCODE -5001/SQLSTATE 42703 – Segment name specified in the SQL statement is not valid. SQLCODE -158/SQLSTATE 42811 – The number of output fields specified in the SQL statement and the number of host variables to be fetched in the FETCH statement is not the same.
  • 25.
    IMS 13 Defining SQLdescriptor areas (SQLDA) SQLDA stores information about prepared SQL statements or host variables. can be read by either the application program or IMS Required for following SQL statements – DESCRIBE statement-name INTO descriptor-name 07- IMS Application: 25 – DESCRIBE CURSOR host-variable INTO descriptor-name – DESCRIBE INPUT statement-name INTO descriptor-name – DESCRIBE TABLE host-variable INTO descriptor-name – EXECUTE … USING DESCRIPTOR descriptor-name – FETCH … INTO DESCRIPTOR descriptor-name – OPEN … USING DESCRIPTOR descriptor-name – PREPARE … INTO descriptor-name
  • 26.
    IMS 13 IMS NativeSQL Terminology The set of SQL statements can be classified into three main categories: – Data Definition Language (DDL) to manage and define database – Data Manipulation Language (DML) to access and manipulate data • SQL support provided is only for data manipulation – Data Control Language (DCL) to control data access Static SQL statements – segment and field names associated with the SQL calls are defined in the 07- IMS Application: 26 – segment and field names associated with the SQL calls are defined in the program source – pre-processed when the program is being pre-compiled • Parsed, validated and converted into an executable form Dynamic SQL statements – statement is constructed and prepared at run time – segment and field names associated with the SQL calls are not known at pre-compile time – parsed and validated by the database at runtime each time the SQL statement is executed.
  • 27.
    IMS 13 IMS COBOLHost Structure Variables and Static SELECT SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 27 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). MOVE ‘LAST1’ to LNAME. EXEC IMSSQL SELECT LNAME, FNAME, ZIP INTO :LNAME,:FTNAME,:ZIP FROM DFSIVP37.TELEPCB WHERE LNAME = :LNAME END-EXEC.
  • 28.
    IMS 13 IMS COBOLHost Structure Variables and Static INSERT SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 28 EXEC IMSSQL INSERT INTO DFSIVP37.TELEPCB (LNAME, FNAME,EXT,ZIP) VALUES (’ALAST’, ’BFIRST’, ’555-5555’,’9999999’) END-EXEC. 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7).
  • 29.
    IMS 13 IMS COBOLHost Structure Variables and Static UPDATE SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 29 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). EXEC IMSSQL UPDATE DFSIVP37.TELEPCB SET FNAME = ‘FIRSTB’ WHERE LNAME=’ALAST’ END-EXEC.
  • 30.
    IMS 13 IMS COBOLHost Structure Variables and Static DELETE SQL call ********************************* * Host Variable declarations * ********************************* 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 07- IMS Application: 30 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). EXEC IMSSQL DELETE FROM DFSIVP37.TELEPCB WHERE LNAME=’ALAST’ END-EXEC.
  • 31.
    IMS 13 Difference betweenStatic and Dynamic SQL Flexibility of static SQL with host variables – When you use static SQL, you cannot change the form of SQL statements unless you make changes to the program. However, you can increase the flexibility of static statements by using host variables. Example: In the following example, the UPDATE statement can update an employee. at run time determine employee to be updated. 01 DCLTELEPCB. 07- IMS Application: 31 01 DCLTELEPCB. 02 LNAME PIC X(10). 02 FNAME PIC X(10). 02 EXT PIC X(10). 02 ZIP PIC X(7). Get FNAME and LNAME input values EXEC IMSSQL UPDATE DFSIVP37.TELEPCB SET FNAME = FNAME INPUT value WHERE LNAME= LNAME INPUT value END-EXEC.
  • 32.
    IMS 13 IMS COBOLDynamic SQL sample Dynamic SQL processing – A program that provides for dynamic SQL accepts as input, or generates, an SQL statement in the form of a character string WORKING-STORAGE SECTION. 05 SELECT-STATEMENT. 49 SELECT-STATEMENT-LEN PIC S9(4) COMP VALUE +39. 49 SELECT-STATEMENT-TXT PIC X(32). 07- IMS Application: 32 49 SELECT-STATEMENT-TXT PIC X(32). PROCEDURE DIVISION. EXEC IMSSQL DECLARE TELE1 CURSOR FOR DYSQL END-EXEC. EXEC IMSSQL WHENEVER SQLERROR GOTO 100-DBERROR END-EXEC.
  • 33.
    IMS 13 IMS COBOLDynamic SQL sample.. MOVE "SELECT LNAME,FNAME,ZIP FROM DFSIVP37.TELEPCB" TO SELECT- STATEMENT-TXT. EXEC IMSSQL PREPARE DYSQL FROM :SELECT-STATEMENT END-EXEC. EXEC IMSSQL 07- IMS Application: 33 OPEN TELE1 END-EXEC. EXEC IMSSQL FETCH TELE1 INTO :PHONEBOOK END-EXEC. EXEC IMSSQL CLOSE TELE1 END-EXEC.
  • 34.
    IMS 13 Solution Highlights SQLsupport for COBOL – Offer SQL as a query language for COBOL programs to access IMS database – EXEC IMSSQL as the interface to execute SQL calls to IMS databases – IMS co-processor in the COBOL Compiler 07- IMS Application: 34 – IMS co-processor in the COBOL Compiler – SQL calls are validated semantically and translated at runtime • No bind process at deployment time
  • 35.
    IMS 13 IMS UniversalDriver Enhancements 07- IMS Application: 35
  • 36.
    IMS 13 Support forNative SQL Processor IMS Universal Drivers use IMS common SQL processing SQL conversion is moved from the client to IMS Migration – IMS Universal Drivers built-in migration support Benefit 07- IMS Application: 36 Benefit – provides consistent SQL processing for all SQL calls – reduces the amount of processing on the client side and the amount of network traffic for data aggregation
  • 37.
    IMS 13 Support fornative SQL Native SQL Processor IMS 13 IMS JDBC Driver ODBM IMS Connect Client Application DRDADRDA MPP,IFP,BMP Regions COBOL Native SQL 07- IMS Application: 37 RYO IMS .NET IMS DB IMS Catalog IMS JDBC Driver Java Enabled Regions Type 4 Type 2 IMS DB IMS Catalog SQL SQL IMS Native SQL Processor DLI z/OS SQL
  • 38.
    IMS 13 DBDGEN Supportfor Unsigned Data Types APAR - PM64418 PTF - UK79443 Problem – IMS Explorer generates DBD source which includes new unsigned data types and internal type converters which are not supported by DBDGEN – Large positive signed 2's complement data element values are made negative. 07- IMS Application: 38 negative. Solution – FIELD macro add support UBYTE, UINT, ULONG, and USHORT data type values on the DATATYPE= parameter – DFSMARSH macro add support UBYTE, UINT, ULONG, and USHORT internal type converter values on the INTERNALTYPECONVERTER= parameter
  • 39.
    IMS 13 DBDGEN FIELDStatement >>-FIELD >-DATATYPE= +-ARRAY +-BINARY +-BIT +-BYTE +-UBYTE +-CHAR +-DATE 07- IMS Application: 39 +-DECIMAL(pp,ss) +-DOUBLE +-FLOAT +-INT +-UINT +-LONG +-ULONG +-OTHER +-SHORT +-USHORT +-STRUCT +-TIME +-TIMESTAMP +-XML
  • 40.
    IMS 13 DBDGEN DFSMARSHStatement >>-DFSMARSH >--+-,INTERNALTYPECONVERTER= +-CHAR +-BIT +-BINARY 07- IMS Application: 40 +-BYTE +-UBYTE +-SHORT +-USHORT +-INT +-UINT +-LONG +-ULONG +-FLOAT +-DOUBLE +-PACKEDDECIMAL +-ZONEDDECIMAL +-CLOB +-BLOB +-XML_CLOB +-ARRAY +-STRUCT
  • 41.
    IMS 13 IMS UniversalDrivers Unsigned Type Support APAR - PM62076 PTF - UK80831 Problem – NullPointerException in the IMS Universal Drivers when accessing a Secondary Index with a single source field that is defined as packed decimal. Solution The IMS Universal Drivers have added support for the following data types: – UTINYINT 07- IMS Application: 41 – UTINYINT – USMALLINT – UINTEGER – UBIGINT Benefits – Users are now able to access a Secondary Index with a single source field of any type – Users will no longer receive an UnsupportedTypeConversion message when accessing a variable length segment where the length field (LL) is defined as BINARY.
  • 42.
    IMS 13 COBOL dataformats mapped to IMS Java data constants PIC X(25) CHAR PIC 9(02) COMP UTINYINT PIC S9(04) COMP SMALLINT PIC 9(04) COMP USMALLINT PIC S9(06) COMP-4 INTEGER PIC 9(06) COMP-4 UINTEGER 07- IMS Application: 42 PIC 9(06) COMP-4 UINTEGER PIC 9(06)V99 COMP or COMP-4 BINARY PIC S9(12) BINARY BIGINT PIC 9(12) BINARY UBIGINT COMP-1 FLOAT COMP-2 DOUBLE PIC S9(06)V99 ZONEDDECIMAL PIC 9(06).99 ZONEDDECIMAL PIC S9(06)V99 COMP-3 PACKEDDECIMAL