Margaret J. Sliming DivaProgrammer, LLC Columbia, SC

1. Creating Thin Client Applications with IDMS, COBOL, ADS and Java Margaret J. Sliming DivaProgrammer, LLC Columbia, SC IDMS and the WEB

2. Contents • Introduction • IDMS Server Architecture • Setting Up CA IDMS Server • Defining the CA IDMS System Using CAICCI • Defining the CA IDMS System Using TCP/IP • Setting Up Database Access • Mainframe Access Components • Appendix IDMS and the WEB

3. Introduction • The purpose of this presentation is to demonstrate how to build thin-client web applications which access IDMS non-SQL databases. • The Java programs simply display the data passed from the mainframe and send the entered data to the mainframe. • Drop down menus on the java screens replace the need for ADS edit and code tables. • Java may do editing for numeric data and date format but any content validation is done on the mainframe • Utilizing this approach make the applications candidates for Cloud computing and being accessible from other platforms such as cell phones and tablets. IDMS and the WEB

4. Introduction (Con’t) • This approach does not use SQL to retrieve data but instead uses DML derived from SQL commands executed by web programs. ADS SQL procedures are initiated by Calls to the dialogs and COBOL table procedures are initiated by SQL commands. • The required components are: • Web programming language such as Java or .NET • CA-IDMS Server • ODBC or JDBC driver • IDMS SQL • CA-IDMS access module (View / Table Procedure / ADSO SQL Procedure) IDMS and the WEB

5. IDMS Server Architecture The following diagram illustrates how the CA IDMS Server JDBC driver software components fit together from any Java platform when used with CA IDMS r16 SP2 or later. The Type 4 JDBC driver and JDBC Server communicate from any Java platform directly to the CV using TCP/IP. IDMS and the WEB

6. IDMS Server Architecture Components • BROWSER: Web application users have a browser (Internet Explorer) running on their PC to access the application. The browser issues Hyper Text Transfer Protocol Secured (HTTPS) requests to a Web server hosting the web application. Web server performs the requested operation and renders the response to the browser. The Sun Java Plug-In is required to use the JDBC driver with applets running in Internet Explorer. The Java Plug-In is installed with the Sun JRE. • WEB SERVER:The Web server receives HTTPS requests from the browser and invokes the web application. Once the requested operation is performed by the application, the Web server renders the HTTPS response to the browser. The Web server which hosts the web application is Sun Java System Web Server (SJSWS), which runs on Sun Solaris. No native SQL interface is installed on this Platform. IDMS and the WEB

7. IDMS Server Architecture Components • WEB APPLICATION: Web Application represents all Java classes, Servlets and Java Server Pages (JSP) which runs within the web Server. All the data access, validation and display logic resides within the web application. • JDBC SERVER: The JDBC Server runs under USS on the Mainframe. It is a middle-tier component connecting a JDBC driver with the database. This is typically used when there is no native SQL client installed on the Web server. It receives SQL calls from the JDBC driver and translates it into IDMS specific calls. It uses the native IDMS SQL client on USS to communicate to IDMS database on the mainframe. • Type 3 JDBC driver: Uses a generic network protocol to communicate with a middleware server that invokes the native client interface to communicate with the database. It uses no native code on the client platform. IDMS and the WEB

8. IDMS Server Architecture Components • SQL CLIENT INTERFACE: SQL Client represents the native SQL component for IDMS database. JDBC Server uses this to talk to IDMS Database. • SQL OPTION: The SQL option provides SQL support for the existing IDMS Database. It enables applications to access IDMS data using SQL. It also facilitates SQL access to non-SQL defined databases through Table Procedures and SQL Procedures. • Type 4 JDBC driver: Communicates directly with the database using its proprietary protocol. It uses no native code on the client platform. i IDMS and the WEB

9. Setting Up CA IDMS Server Each CV to be accessed by CA IDMS server must be generated with the following definitions: • A CCI line to use the ODBC driver or JDBC driver with the CCI protocol. • A CASERVER task for the CCI line • A TCP/IP line to use the ODBC or JDBC deriver with the IDMS TCP/IP protocol. • A listener PTERM for the TCP/IP line • An IDMSJSRV task for the TCP/IP line • A PTERM/LTERM pair for each concurrent connection on each line • The SQL definitions in the catalog area of the dictionary associated with the CA IDMS database • The startup JCL may also need to be modified for the TCP/IP line. IDMS and the WEB

10. Defining the CA IDMS System Using CAICCI • CAICCI provides communication between mainframes or between mainframes and PCs. CAICCI r2.1 and higher provides Secure Sockets Layer (SSL) support for the ODBC and JDBC drivers. The following diagram illustrates the sample CA IDMS system network using CAICCI: IDMS and the WEB

11. Defining the CA IDMS System Using TCP/IP • TCP/IP provides direct connection between a client system using the JDBC type 4 driver and CA IDMS r16 SP2 or later, or using the ODBC wire protocol driver and CA IDMS r17 or later. • Refer to the IDMS Server Guide for more information on how TCP/IP greatly improves performance over CAICCI. • The following diagram illustrates the sample CA IDMS system network using TCP/IP: IDMS and the WEB

12. Setting Up Database Access • The ODBC and JDBC drivers use dynamic SQL to access a CA IDMS database from an ODBC or JDBC application. • Both the SQL Option and the host component of CA IDMS Server must be installed on the CV. • The database can be defined using the Schema compiler or SQL Data Description Language (DDL). • In either case, you must include the appropriate SQL definitions in the dictionary associated with the CA IDMS system. • The SQL definitions reside in the catalog area of the dictionary. IDMS and the WEB

13. Mainframe Access Components • SQL Schemas • VIEWS • COBOL Table Procedures • ADSO SQL Procedures IDMS and the WEB

14. SQL Schemas • An SQL schema creates a logical relationship to the IDMS schema for SQL functions. Command: CREATE SCHEMA sql-schema-name FOR NONSQL SCHEMA nonsql-schema-name; • SQL schemas are also created for views, table procedures and ADS procedures. These schema names are the prefix for the procedure definitions which pass data between the mainframe and web. • For this presentation, views will have a schema name of CCTSVIEW, table procedures will use CCTSTBLP and ADS procedures will have CCTSPROC. IDMS and the WEB

15. What is a View? • A view is a combination of a table definition and query executed from a client server program or IDMS command facility against a non-SQL IDMS database. A view returns a table of data meeting it’s selection criteria. • A view uses the SQL schema which corresponds to the non-SQL IDMS schema, so it can access all of the same records in the database that could be retrieved using OLQ, Culprit or DMLO, etc. IDMS and the WEB

16. Sample VIEW Java Code SQL Table Web Page VIEW Definition Data Base Mainframe IDMS and the WEB

17. What is a Table Procedure? • A table procedure is a combination of a COBOL program and a procedure definition which enables IDMS to: Accept data from Java or IDMS Apply updates to an IDMS database Pass data back to the Java program or IDMS command facility • COBOL table procedures a best suited to returning multiple rows or records of data where ADS procedures are better to use when only one occurrence of a record is needed. IDMS and the WEB

18. Defining a Table Procedure • The Table Definition is created using the ‘CREATE TABLE PROCEDURE’ command. • Table procedures are executed using ‘SELECT’ statements. They can be issued in a Java program, from the IDMS Online Command Facility (OCF) or using the IDMS Batch Command Facility (BCF). • The ‘SELECT’ statement “Where” clause controls which records are to be returned and overrides the COBOL program logic. IDMS and the WEB

19. Controlling Record Selection • Table Procedure programs are called by IDMS server and fields called “SQLSTATE” and “SQL-OP-CODE” are used to determine which functions to execute in the program and to also communicate with IDMS Server. • “SQLSTATE” is similar to the IDMS error status codes, which are made up of major and minor codes. SQLSTATE codes are 5-bytes with the first two being the class and the remaining three being the sub-class. • For this presentation, we are only concerned with classes: • 00 - Successful completion • 02 - No data • 38 - External routine exception IDMS and the WEB

20. Controlling Record Selection (cont’d) • SQL-OP-CODE is assigned by IDMS server and passed to the COBOL table procedure program, which performs the appropriate function based on it’s value. SQL-OP-CODE can have the following values: IDMS and the WEB

21. SQL-OP-CODE SQLSTATE Controlling Record Selection (cont’d) IDMS Server COBOL Program IDMS and the WEB

22. Controlling Record Selection (cont’d) • When an SQL command is executed, IDMS Server calls the requested COBOL program for the first time with SQL-OP-CODE having a value of +12 (OPEN SCAN). • This signals the COBOL program to perform it’s Initial processing of setting variable values and readying the database. If the SQL command is a “SELECT”, the SQL-OP-CODE will subsequently contain a value of +16 (Next Row) until an END-OF-SET condition is reached. • When this occurs, the COBOL program will pass back a value of 02000 (No Data) in SQLSTATE to alert IDMS server that end of processing has occurred. • IDMS server will then call the COBOL program one last time with an SQL-OP-CODE of +20 (Close Scan) to perform final processing. An SQLSTATE value with a class of 38 (Unrecoverable Error) will also stop processing and optionally display a message in the IDMS log. IDMS and the WEB

23. Sample COBOL Table Procedure – Mainframe Screen IDMS and the WEB

24. Sample COBOL Table Procedure Java Code SQL Table Data Base Program Code Web Page Mainframe IDMS and the WEB

25. What is an ADSO SQL Procedure? An ADSO SQL Procedure is a combination of a mapless dialog and a table record definition which can be “CALLED” from a Java program or IDMS Online Command Facility (OCF). This interface is much easier to use because IDMS server is not controlling the logic flow. The procedure record is defined as a work record so information is received from it and passed to it as in any other dialog. IDMS and the WEB

26. Defining an ADSO SQL Procedure • The Procedure Record Definition is created using the ‘CREATE PROCEDURE’ command. • A new schema called CCTSPROC has been created. This schema will be used for ADSO SQL Procedures. ‘DRD305X9’ defined in the ‘EXTERNAL NAME’ parameter is the name of the ADSO mapless dialog. IDMS and the WEB

27. Sample ADS Procedure – Mainframe Screen IDMS and the WEB

28. Sample ADS Procedure Java Code SQL Table Data Base ADS Code Web Page Mainframe IDMS and the WEB

29. APPENDIX • Steps for creating SQL Procedure mapless dialogs from existing ADSO dialogs. • SQLSTATE Values • Additional Information IDMS and the WEB

30. IDMS Manuals CA IDMS Server User Guide CA-IDMS System Generation CA-IDMS System Operations CA-IDMS Database Administration - 1 CA-IDMS Database Administration - 2 CA-IDMS SQL Reference CA-IDMS SQL Programming Guide CA-IDMS DML Reference – COBOL CA-IDMS ADS Reference Guide White Papers and Presentations CA IDMS™ Server r17 Server Option and WebApplications - Troubleshooting and Performance Tuning Using CA IDMS™ in the cloud Achieving application resilience ADS SQL Procedures CA-IDMS/DB SQL Table Procedures: Introduction Additional Information Sources used for this presentation were the design documents for a related project, IDMS manuals and information from the CA website. A list of useful documents when setting up CA-IDMS System to work with CA-IDMS Server: IDMS and the WEB