Transcript What is a Table Procedure?

Creating Thin Client
Applications with IDMS,
COBOL, ADS and Java
Margaret J. Sliming
DivaProgrammer, LLC
Columbia, SC
4/11/2016
IDMS and the WEB
A
1
R
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
A
4/11/2016
IDMS and the WEB
2
R
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.
4/11/2016
IDMS and the WEB
A
3
R
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)
A
4/11/2016
IDMS and the WEB
4
R
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.
A
4/11/2016
IDMS and the WEB
5
R
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.
4/11/2016
IDMS and the WEB
A
6
R
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.
4/11/2016
IDMS and the WEB
A
7
R
IDMS Server Architecture Components



Type 4 JDBC driver: Communicates directly with
the database using its proprietary protocol. It uses no
native code on the client platform.
SQL CLIENT INTERFACE: SQL Client represents the native
i
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 nonSQL defined databases through Table Procedures and SQL
Procedures.
A
4/11/2016
IDMS and the WEB
8
R
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.
A
9
R
4/11/2016
IDMS and the WEB
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:
A
4/11/2016
IDMS and the WEB
10
R
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:
A
4/11/2016
IDMS and the WEB
11
R
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.
A
4/11/2016
IDMS and the WEB
12
R
Mainframe Access Components




SQL Schemas
VIEWS
COBOL Table Procedures
ADSO SQL Procedures
A
4/11/2016
IDMS and the WEB
13
R
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.
4/11/2016
IDMS and the WEB
A
14
R
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.
A
4/11/2016
IDMS and the WEB
15
R
Sample VIEW
Web Page
Java Code
SQL Table
VIEW Definition
Data Base
A
Mainframe
4/11/2016
IDMS and the WEB
16
R
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.
A
4/11/2016
IDMS and the WEB
19
R
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.
A
4/11/2016
IDMS and the WEB
20
R
Controlling Record Selection



Table Procedure programs are called by IDMS
server and fields called “SQLSTATE” and “SQLOP-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
4/11/2016
IDMS and the WEB
A
21
R
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:
SQL-OPEN-SCAN
VALUE +12.
SQL-NEXT-ROW
VALUE +16.
SQL-CLOSE-SCAN
VALUE +20.
SQL-SUSPENDSCAN
VALUE +24.
SQL-RESUMESCAN
VALUE +28.
SQL-INSERT-ROW
VALUE +32.
SQL-DELETE-ROW
VALUE +36.
SQL-UPDATE-ROW
VALUE +40.
A
4/11/2016
IDMS and the WEB
22
R
Controlling Record Selection (cont’d)
IDMS
Server
COBOL
Program
SQL-OP-CODE
SQLSTATE
Server
A
4/11/2016
IDMS and the WEB
23
R
Controlling Record Selection (cont’d)
1) 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).
2) 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 SQLOP-CODE will subsequently contain a value of +16
(Next Row) until an END-OF-SET condition is reached.
3) 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.
4) 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.
4/11/2016
IDMS and the WEB
A
24
R
Sample COBOL Table Procedure – Mainframe Screen
A
4/11/2016
IDMS and the WEB
25
R
Sample COBOL Table Procedure
Web Page
Java Code
SQL Table
Program Code
Data Base
A
Mainframe
4/11/2016
IDMS and the WEB
26
R
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.
4/11/2016
IDMS and the WEB
29
A
R
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.
A
4/11/2016
IDMS and the WEB
30
R
Sample ADS Procedure – Mainframe Screen
A
4/11/2016
IDMS and the WEB
31
R
Sample ADS Procedure
Web Page
Java Code
SQL Table
ADS Code
Data Base
A
Mainframe
4/11/2016
IDMS and the WEB
32
R
APPENDIX

Steps for creating SQL Procedure
mapless dialogs from existing
ADSO dialogs.

SQLSTATE Values

Additional Information
A
4/11/2016
IDMS and the WEB
35
R
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 Manuals









White Papers and
Presentations
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
4/11/2016






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
IDMS and the WEB
A
37
R