Transcript Enterprise Generation Language

IBM Software Group | Rational Software
Access to System z data
Mark Evans
[email protected]
© 2007 IBM Corporation
IBM Software Group | Rational Software
– © Copyright IBM Corporation 2007. All rights reserved.
– The information contained in these materials is provided for informational purposes
only, and is provided AS IS without warranty of any kind, express or implied. IBM
shall not be responsible for any damages arising out of the use of, or otherwise
related to, these materials. Nothing contained in these materials is intended to, nor
shall have the effect of, creating any warranties or representations from IBM or its
suppliers or licensors, or altering the terms and conditions of the applicable license
agreement governing the use of IBM software. References in these materials to
IBM products, programs, or services do not imply that they will be available in all
countries in which IBM operates.
– This information is based on current IBM product plans and strategy, which are
subject to change by IBM without notice. Product release dates and/or capabilities
referenced in these materials may change at any time at IBM’s sole discretion based
on market opportunities or other factors, and are not intended to be a commitment to
future product or feature availability in any way.
– IBM, the IBM logo, the on-demand business logo, Rational, the Rational logo, and
other IBM Rational products and services are trademarks or registered trademarks
of the International Business Machines Corporation, in the United States, other
countries or both. Other company, product, or service names may be trademarks or
service marks of others.
2
© 2003 IBM Corporation
IBM Software Group | Rational Software
Agenda – Access to Mainframe Data
 What’s Possible?
 SQL Access
– How to do it?
– Configuration Possibilities
– Specifying Connection Information
– Specifying Jar File Locations
– Miscellaneous
 Access to Files
– Remote
– From Generated COBOL programs
 Lab accessing remote DB2 on z/OS
3
© 2003 IBM Corporation
IBM Software Group | Rational Software
Data Access from EGL – local on System z
 From Generated COBOL
– Serial
– Indexed, relative keys (VSAM)
– DB2
– DL/I
– MQ Queues
– CICS
• Transient Data Queues
• Temporary Storage Queues
• Spool
– IMS GSAM files
4
© 2003 IBM Corporation
IBM Software Group | Rational Software
Remote Access from EGL – SQL – Controlling Access
 Database - DB2,Oracle, etc
• Requires JDBC driver and access to jar/zip file containing the class
> Example: com.ibm.db2.jcc.DB2Driver
• Connection to database defined via Connection URL
> Example: jdbc:db2://localhost:50000/SAMPLE
• EGL does not know or care that it is connecting to a remote
database (System i or System z)
It just connects to a URL (non-J2EE) or JNDI name (J2EE)
 “Remote Access” -- means from debugger and from
WAS deployed apps
5
© 2003 IBM Corporation
IBM Software Group | Rational Software
Remote Access from EGL – DB2 on z/OS
 Access done through DB2 supplied JDBC Drivers
– Choice of two drivers:
• DB2 Universal JDBC Driver
• DB2 App Driver
 Choosing the Universal Driver vs. the App Driver
– DB2 Universal JDBC Driver (recommended):
+
+
+
+
Most Modern Driver from DB2
No infrastructure to setup
Supports Scrollable cursors from EGL
Will be pre-requisite for other EGL functionality in future
- Cannot control DATE/TIMESTAMP format when EGL variable defined as CHAR
-
Always returned/expected in ISO format
– DB2 App Driver
+ Allows control of DATE/TIMESTAMP format when EGL variable defined as CHAR
- More infrastructure setup required
- To be deprecated in future
6
© 2003 IBM Corporation
IBM Software Group | Rational Software
Remote Access from EGL – DB2 on z/OS
 What’s required to make this work (Universal Driver)?
– IBM Universal JDBC Driver + Valid DB2 Connect License Jar
•
•
DB2 V8.1 or later
No testing done with V9 yet
•
Just supply access information in URL
•
Reference Doc: DB2Unicon.doc
DB2 Host Name
Jar file for Universal Driver
7
DB2 Host Port
DB2 Location
License Jar file for Remote z/OS connection
© 2003 IBM Corporation
IBM Software Group | Rational Software
Remote Access from EGL – DB2 on z/OS
 What’s required to make this work (App Driver)?
– IBM App Driver + Valid DB2 Connect Installed (full product somewhere + Client access)
•
•
DB2 V8.1 or later AND DB2 Connect V8.1 or later
No testing done with V9 yet
•
Database and remote nodes must be defined in DB2 catalogs
DB2 Catalog Definition for remote Database
Zip File containing App Driver
8
© 2003 IBM Corporation
IBM Software Group | Rational Software
Remote Access from EGL – DB2 on z/OS
 Miscellaneous Topics
– Allows user to Specify a Secondary Authentication ID to “set Current SQLID” to specify
schema on unqualified table names in record definitions
– Setting date/timestamp format for DB2 App driver (if needed)
•
Requires at least DB2 V8.1 Fix pack 10/DB2 8.2 Fix pack 3)
•
•
Open DB2 Command Window
Run the following command:
db2 update cli cfg for section COMMON using DateTimeStringFormat USA (or EUR/ISO/…)
•
You can check the current configuration by running the following command:
db2 get cli cfg
9
© 2003 IBM Corporation
IBM Software Group | Rational Software
Specifying Connection Info to databases – Where do I do it?
We covered the basics on what is needed to access
remote DB2 databases
Now let’s look at how you specify this remote
access during development and when executing
generated Java programs!!
10
© 2003 IBM Corporation
IBM Software Group | Rational Software
Specifying Connection Info to databases – Where do I do it?
 SQL Retrieve
 SQL Statement
Validation
 EGL Debugger if not in
debug build descriptor
– Use EGL SQL
Database Connection
Preference
11
© 2003 IBM Corporation
IBM Software Group | Rational Software
Specifying connection info to databases – Where do I do it?
 EGL Data Parts Wizard
– Connection information
can be created in
Wizard
or
– Through Database
Explorer view in the
Data Perspective
It is the same wizard
12
© 2003 IBM Corporation
IBM Software Group | Rational Software
Specifying Connection Info to databases – Where do I do it?
 For Web Applications (J2EE)
– JNDI Name in build descriptor
– JNDI reference in web.xml
– WAS Data source Definition with
associated JNDI name
13
© 2003 IBM Corporation
IBM Software Group | Rational Software
Specifying Connection Info to databases – Where do I do it?
 For non-J2EE Generated Java Apps
and
 Debug
– In Build descriptor
• sqlDB
– Connection URL
• sqlJDBCDriverClass
– Class to use for connection
– Which results in
Rununit.properties
Found in JavaSource folder
14
© 2003 IBM Corporation
IBM Software Group | Rational Software
Adding Jar file to Classpath for JDBC Drivers – Where do I do it?
Now let’s look at how you setup your projects/systems to
find the jar files with the JDBC drivers
“Avoid the dreaded “ClassNotFound” exception for the
JDBC classes”
15
© 2003 IBM Corporation
IBM Software Group | Rational Software
Adding Jar file to Classpath for JDBC Drivers – Where do I do it?
 For J2EE Apps:
– WAS JDBC Provider definition
plus
– WAS WebSphere Variables
16
© 2003 IBM Corporation
IBM Software Group | Rational Software
Adding Jar file to Classpath for JDBC Drivers – Where do I do it?
 For non-J2EE Generated Java
Apps
and
 Debug (if not using EGL
Preferences)
– In Java Build Path of Project
Properties
NOTEs:
 If you export and import
project into another
workspace, this hard coded
location may fail.
 On deployment, the DB2 jar
files WILL NOT be exported
with the resultant jar file. You
will need to add to
CLASSPATH
17
© 2003 IBM Corporation
IBM Software Group | Rational Software
Adding Jar file to Classpath for JDBC Drivers – Where do I do it?
 Debug (if using EGL
Preferences)
– Mainly used for customers
who are only generating
COBOL for deployed apps
18
© 2003 IBM Corporation
IBM Software Group | Rational Software
Miscellaneous - SQL Retrieve Preferences for COBOL Gen
record employee type SQLRecord
{tableNames = [["DRBRUCE.EMPLOYEE"]]}
10 EMPNO char(6)
{column="EMPNO", isNullable=yes};
10 FIRSTNME char(12)
{column="FIRSTNME", isNullable=yes;
10 MIDINIT char(1)
{column="MIDINIT", isNullable=yes};
10 LASTNAME char(15)
{column="LASTNAME", isNullable=yes;
10 HIREDATE char(10)
{column=“HIREDATE", isNullable=yes};
10 ..
VERSUS
record employee type SQLRecord
{tableNames = [["DRBRUCE.EMPLOYEE"]]}
EMPNO string
{column="EMPNO", isNullable=yes, maxLen=6};
FIRSTNME string
{column="FIRSTNME", isNullable=yes,
maxLen=12};
MIDINIT string
{column="MIDINIT", isNullable=yes, maxLen=1};
LASTNAME string
{column="LASTNAME", isNullable=yes,
maxLen=15};
HIREDATE date
19
{column=“HIREDATE", isNullable=yes};
© 2003 IBM Corporation
IBM Software Group | Rational Software
Other Remote Data Access from EGL Debugger and Java Gen
 Indexed, relative keys (VSAM)
• Uses EGL supplied Distributed File Manager support
– VSAMWIN.zip in plug-in directories
• SNA (or TCP62) based
– Requires SNA client product (like IBM Personal Communications) to
supply protocol drivers
• Reference doc:
WDz6RemoteVSAM.pdf
– IMS/DB (alias DL/I)
• Not supported in V6
20
© 2003 IBM Corporation
IBM Software Group | Rational Software
File Access from Generated COBOL
 Controlling/Specifying Access
– Files
• Logical file name is property of record definition
• Association to physical file name defined using EGL “resource association”
– Another build file part
– Also associates to runtime logical names such as:
> JCL DD Names
> CICS File Definition Entries
21
© 2003 IBM Corporation
IBM Software Group | Rational Software
Build Parts – Resource Association
 Is needed only for EGL record that accesses an external file, printer, or MQ
 Has a logical file or queue name
 Like JCL in a batch application if serial files
 See the topic in the tutorial: Conceptual View of External File Access in EGL
 Allows different mappings depending on target platform
22
© 2003 IBM Corporation
IBM Software Group | Rational Software
Build Parts – Resource Association - Relationships
Uses this
Build
Descriptor
Resource Association
Part referenced from
Build Descriptor Part
Association element can
be associated with
multiple target platforms
and file type
23
combinations
Record queuename property
points to Association element
in Resource Association Part
Each target platform and
file type combination
has its own properties
© 2003 IBM Corporation
IBM Software Group | Rational Software
Available Materials (Access to Remote Resources)
 Documents on Developerworks
– DB2Unicon.doc
- database setup for DB2 on z/OS access
– iSeriesTutorial.doc - includes database setup for DB2 on iSeries access
 Materials from class:
– Lab document
– db2oniSeriesSetup.doc
- database setup for DB2 on iSeries
– WDz6RemoteVSAM.pdf
- Remote VSAM access to z/OS from EGL
debugger
24
© 2003 IBM Corporation
IBM Software Group | Rational Software
Backup Charts – for System i
25
© 2003 IBM Corporation
IBM Software Group | Rational Software
Data Access from EGL – local on System i
 From Generated COBOL
– File Access
• Serial
• Indexed
• Relative Record
– DB2
– MQ Queues
Note: Data Queues are accessible only through called CL
programs, not direct.
26
© 2003 IBM Corporation
IBM Software Group | Rational Software
Remote Access from EGL – DB2 on System i
 Remote access -- means from debugger and from WAS deployed apps
 Access done through System i Java Toolkit supplied JDBC Driver
URL must specify as400
Host Name of System i machine
JT400 jar file containing AS400JDBCDriver class
27
© 2003 IBM Corporation
IBM Software Group | Rational Software
Getting the JT400 Toolkit for DB2 access – System i ?
or
 With WDSC, in the following directory
– <% Install directory %> \rwdi_shared\eclipse\plugins\com.ibm.etools.iseries.toolbox_6.0.1\runtime
or
 From the System i directories
– \QIBM\ProdData\HTTP\Public\j4400\lib
28
© 2003 IBM Corporation