Appendices

1
Appendices
Unit
Topics

• Connecting to External Data Sources on the i5

2
Topics Access to Mainframe System i and
System z Data

• Whats 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 DB2

System z or IBM i
EGL Logic Part Data Access
Note Original materials by Mark Evans -
evansm_at_us.ibm.com
3
Options - Non-Relational Data Access from EGL
(COBOLGEN only)

• System z - from Generated COBOL
• Serial files (QSAM files on the host)
• Indexed, relative keys (VSAM files on the host)
• CICS
• Transient Data Queues
• Temporary Storage Queues
• Spool
• System i - from Generated COBOL
• File Access
• Serial
• Indexed
• Relative Record
• Note Data Queues are accessible only through
  called CL programs, not direct.

4
Other Remote Data Access from EGL Debugger and
Java Gen Parts

• QSAM sequential files
• AIX, Windows only
• As standard external files, defined as resource
  associations
• VSAM - Indexed, relative key files
• 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

5
Relational SQL Data Access from EGL (Java Gen)

• Control/Specify access to DB2,Oracle, Derby,
  MS-SQLServer, etc)
• Requires JDBC driver and access to jar/zip file
  containing the class
• Example com.ibm.db2.jcc.DB2Driver
• Connect to database via Connection URL
• Example jdbcdb2//localhost50000/SAMPLE
• Or, connect to database via JNDI name
• Example jdbc/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)
• JNDI name (J2EE)

6
Remote Access from EGL DB2 on z/OS

• Whats required to make this work?
• IBM Universal JDBC Driver Valid DB2 Connect
  License Jar Notes
• DB2 V8.1 or later
• Just supply access information
• Reference Doc DB2Unicon.doc

DB2 Host Name
DB2 Host Port
DB2 Location
Jar file for Universal Driver
License Jar file for Remote z/OS connection
7
Remote Access from EGL DB2/400 on i5

• Access done through System i Java Toolkit
  supplied JDBC Driver
• Whats required to make this work?
• JT400.jar
• Valid connection information, authentication and
  permissions
• IP Address of the host machine
• User ID
• Password
• System or sql schema

Host Name of System i machine
URL must specify as400
JT400 jar file containing AS400JDBCDriver class
8
Getting the JT400 Toolkit for DB2 access System
i

• or
• With WDSC, in the following directory
• In your installation directory
• or...
• From the System i directories
• \QIBM\ProdData\HTTP\Public\j4400\lib

9
Workshop Connect Your EGLWeb Project to Your i5
DB2/400 Data (Tomcat)

• Assuming you have all the proper authorizations,
  you will do the following
• Create a new connection to your i5 and DB2400
• Make this new connection the default connection
• Customize the Tomcat context.xml to point to
  your i5
• Customize the projects Buildfile with a
  sqlJNDIname property value that connects to your
  Tomcat context.xml files entry (to point to your
  i5)
• Run the Data Access Wizard to generate new
  SQLRecords and Libraries for your i5 DB2 tables
• Create a page that uses the SQLRecords and
  accesses i5 data

10
? Specifying Connection Info to Databases 1 of 4

• Use EGL SQL Database Connection Preference for
• SQL Retrieve
• SQL Statement Validation
• EGL Debugger if not in debug build descriptor
• From Preferences, select SQL Database
  Connections
• Click New

11
? Specifying connection info to Databases 2 of 4

• Select
• DB2 for i5/OS
• Enter the following
• Hostname, or ping-able IP address
• User ID
• Password
• Click
• Test Connection
• Click
• Next gt

12
? Specifying connection info to Databases 3 of 4

• Specify
• ? Disable filter
• ? Selection
• Check
• However many libraries youd like to do
  development in
• Click
• Finish

13
? Specifying connection info to Databases 4 of 4

• Ensure that your new, i5 connection is the
  default Click Apply then OK

14
Specifying Database Connection WebSphere
DB2

• For Web Applications (J2EE)
• sqlJNDI Name in build descriptor
• JNDI reference in web.xml
• Project EAR File
• Application Deployment Descriptor

15
Specifying Database Connection Tomcat
DB2

• For Web Applications (J2EE)
• sqlJNDI Name in build descriptor
• context.xml (Resource driverClassname) in
  \WebContent\META-INF

16
Connecting a Tomcat Application Server to an
AS/400 Database

• You will need to do the following, to connect
  your Tomcat Server to an AS/400 Database
• Add the necessary jdbc client/driver .JAR files
  to youre the tomcat \common\lib\ folder ?
• Customize a ltResource entry in your projects
  context.xml
• Here is an example of such a ltResource entry for
  an AS/400 JDBC driver
• ltResource driverClassName"com.ibm.as400.access.AS
  400JDBCDriver"
• maxActive"4" maxIdle"2" maxWait"5000"
  auth"Container"
• name"jdbc/ltyourlogicalJNDInamegt"
  password"xxxxxxx"
• type"javax.sql.DataSource"
• url"jdbcltyour connection URLgtnamingsystem
  "
• usernameltyour user IDgt"/gt

17
? Modify Your Projects context.xml File (add a
new Resource for accessing your i5)

• From Project Explorer
• Open \WebContent\META-INF\context.xml
• From the Slide Notes copy/paste the sample
  ltResource driverClassName into the file as shown
  in the screen capture below. Then modify the
• name and password namejdbc/i5
  passwordyour password
• url (carefully specify your HostName)
• Username (your user id)
• Save your changes

18
? Customize the Projects BuildFile

• From Project Explorer
• Open \EGLSource\EGLWeb.eglbld
• Click into the sqlJNDIName value (Left-click to
  edit)
• Change the sqlJNDIName to match your new i5
  Resource driverClassName jdbc/i5
• Save your changes

19
? Use the Data Access Wizard to Create SQLRecords
and Functions for Accessing your i5 DB2 Tables

• From Project Explorer
• Right-click over \EGLSource\
• Select New gt Other gt
• From Select a wizard
• Expand the EGL category
• Select EGL Data Access Application
• Click Next gt
• From Define project settings
• Open the Project Name combo-box, and select
  EGLWeb
• Open the Database Connection combo-box, and
  select your new i5 named connection
• Select your tables (not too many?)
• Click Next gt

20
? Use the Data Access Wizard to Create SQLRecords
and Functions for Accessing your i5 DB2 Tables

• From Define the Fields
• Choose a key field for each table
• Click Next gt
• From Define project creation options
• Check Qualify table names with schema
• Click Finish

21
? Create a Page to Access your i5 Data

• From Project Explorer
• Right-click over \WebContent\
• Specify New gt Web Page
• Name the page yourPage.jsp
• Modify the boiler-plate heading
  text, and edit the EGL

Using Content Assist (Ctrl/Spacebar) Add a
string variable named searchField Add a variable
for one of your i5 tables - make it an array
0 Add a new function, named searchFunc() In
this function, add a get statement, that
references the i5 table variable
22
? Create a Page to Access your i5 Data

• With your cursor between the get ltvariableNamegt
  statement,
• Right-click
• Select
• SQL Statement
• Add

23
? Create a Page to Access your i5 Data

• Add a Where clause similar to the one shown
  here
• Example
• where ltfieldgt gt searchfield
• Optionally modify the order by clause
• Save your EGL code
• From Project Explorer,

Note colon prefix searchField

• Generate your project

24
? Create a Page to Access your i5 Data

• From Page Designer from Page Data
• Drag searchField onto the page, and create an
  input field with a single Submit Button
• Drag searchFunc on top of the Submit Button
• Drag the i5 table array variable on to the page
  to create a JSF dataTable, Select only a few of
  the columns from your table
• Optionally, drag an HTML Rule between the Submit
  Button and dataTable

25
? Create a Page to Access your i5 Data (optional
styling)

• From Page Designer Properties, with the
  dataTable selected
• From the hxdataTable property Give the
  dataTable a Border
• From Display options, specify
• No Column class (for alternate row colors)
• A scrollable area

26
? Run the Page and Search

• Right-Click over your page, and select Run on
  server
• Enter a valid search criteria and click the
  Search button

27
Database Connections Allowing the Data Source
to Define the Schema
Problem You have multiple DB2 environments. The
tables are named the same across the
environments, but the schema name changes. Ex.
DB2P.EMP, DB2Q.EMP, DB2T.EMP, etc. How can you
pass a different Schema name that qualifies
tables in through your dynamic SQL
statements? Answer You can solve this for each
EAR file. 1. From the EAR files Application
Deployment Descriptor/Deployment tab find the
Data Source, and enter a currentSchema for the
DB2 Schema you wish to qualify the tables
with 2. When you import the SQL from the Data
Access Application Wizard do NOT qualify table
names with schema name (note this was a checkbox
in the wizard) Notes
28
Database Connections Adding Custom Properties
to JDBC Drivers
Problem You need to provide read-only SQL data
access to un-committed rows in your database
(so-called dirty read database access. There
is no property for this in your JDBC connections
under WAS (the default is to allow access only to
committed and un-locked rows. Answer You can
solve this for each Data Source by customizing
the Data Source, and adding a Resource Property
as follows 1. From the EAR files Application
Deployment Descriptor/Deployment tab find the
Data Source, and from the Resource properties
defined in the data source selected, click
Add 2. To allow access to uncommitted rows,
provide the Name/Type and Value shown in the
screen capture 3. To learn more about
customizing a DataSource, read the following IBM
Document http//www-1.ibm.com/support/docview.w
ss?rs180uidswg21224492
29
Database Connections JT400 Setting Binary
Translation on For CCSID Problems

• Problem Why are my web pages returning
  hexadecimal characters to my .JSP page or EGL
  program?
• Answer Normally, JT400 driver will translate
  EBCDIC characters to EGL character data
  automatically. If its sending back hexadecimal
  values, then it is likely that the field in the
  DB2/400 database has a CCSID 65535 property. To
  fix this problem (for web pages)
• From the EAR files Application Deployment
  Descriptor/Deployment tab find the Data Source,
  and from the Resource properties defined in the
  data source selected,
• Scroll down to find the translateBinary property
  entry
• Click Edit
• Value true
• Save your changes to the Deployment Descriptor
• Restart your server

30
Specifying Connection Info to Databases 4 of 4

• For non-J2EE Generated Java Apps and
• Debug (if not using EGL Preferences)
• In Build descriptor
• sqlDB
• Connection URL
• sqlJDBCDriverClass
• Class to use for connection
• Which results in
• rununit.properties entries?
• Found in JavaSource folder

31
Adding Jar file to Classpath for JDBC Drivers 1
of 3

• For J2EE Apps
• WAS JDBC Provider definitionplus
• WAS WebSphere Variables

32
Adding Jar file to Classpath for JDBC Drivers 2
of 3

• For non-J2EE Generated Java Apps
• and
• Debug (if not using EGL Preferences)
• In Java Build Path of Project Properties

33
Adding Jar file to Classpath for JDBC Drivers 3
of 3

• Debug (if using EGL Preferences)
• Mainly used for customers who are only generating
  COBOL for deployed apps

34
Miscellaneous - SQLRetrieve Preferences for COBOL
Gen
record employee type SQLRecord tableNames
"DRBRUCE.EMPLOYEE" 10 EMPNO char(6)
column"EMPNO", isNullableyes 10 FIRSTNME
char(12) column"FIRSTNME", isNullableyes 10
MIDINIT char(1) column"MIDINIT",
isNullableyes 10 LASTNAME char(15)
column"LASTNAME", isNullableyes 10 HIREDATE
char(10) columnHIREDATE", isNullableyes 10
..
Instead of
Produces
record employee type SQLRecord tableNames
"DRBRUCE.EMPLOYEE" EMPNO string
column"EMPNO", isNullableyes,
maxLen6 FIRSTNME string column"FIRSTNME",
isNullableyes, maxLen12 MIDINIT
string column"MIDINIT", isNullableyes,
maxLen1 LASTNAME string column"LASTNAME",
isNullableyes,
maxLen15 HIREDATE date
columnHIREDATE", isNullableyes
35
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
• db2oniSeriesSetup.doc - database setup for DB2
  on iSeries
• WDz6RemoteVSAM.pdf - Remote VSAM access to z/OS
  from EGL
• debugger

36
? Lab1 Configure a Connection for DB2

• From Window gt
• Preferences gt
• EGL gt
• SQL Database Connection
• Create a new connection, and specify parameters
  for DB2 ?
• Note that, if you have DB2 installed locally on
  your machine, you may use DB2 UDB (You will have
  to select the proper version) ?
• If you wish to use DB2 mainframe (either iSeries
  or System z), you will need to read through the
  documentation described on the previous slide
  (Available Materials) for additional steps and
  assistance.
• Press Test Connection to test your settings.
• Click Finish

37
? Lab2 Use the Connection in the Data
Perspective

• From Window
• Open Perspective
• Other
• Data
• Using your skills from the previous unit in
  this course, explore a DB2 database using your
  new Connection
• Here we are showing the SAMPLE DB2 database ?

38
? Lab3 Use the Connection to SQLRetrieve Schema
into an EGL Record

• From the Web Perspective, open CustomerLib.egl.
  Scroll to the bottom, and enter the following
  record statement
• With your cursor inside the statement,
  Right-Click and select SQL Record gt Retrieve
  SQL
• Press Ctrl/S (Save)

Read slide Notes
SAMPLE Database Catalog

• You will need to enter YOUR schema name not
  jsayles

39
? Lab4 Create a Connection From your EAR File
to DB2

• This is a multi-step/complex lab, where you will
  create, modify or connect all of the system files
  and DB2 properties discussed in this Appendix.
  Take your time and triple-check your work.
  Once youve mastered these steps and concepts you
  will be able to connect to your own companys
  data sources. Heres what youll be doing
• (One-time step) Customize your projects EAR file
  entries
• Add a new JAAS Authentication Alias so your
  pages can login and access DB2 data
• Add a new JDBC Provider to DB2 specify a
  logical name that points to your DB2
  client/connectivity software
• Add a new JNDI name a logical name (like a JCL
  //DD card) for your database. When creating this
  logical name, you will specify necessary database
  connection properties
• Database Name, serverName (logical I/P name or
  address), port
• (One-time step) Add a new EGL Build file for your
  DB2 connection
• 4. Add a new build file, and customize its
  sqlJNDIName to match your new JNDI Name
• (One-time step) Add an entry in your Projects
  web.xml file
• 5. Add a new sql resource reference in your
  Projects web.xml file for the new JNDI Name
• Create a new web page
• 6. Create a list page that uses the
  employeeDeptJoin record created in Lab3
• Specify your new DB2 connection EGL Build File
  for the pages JSFHandler
• 7. Your JSFHandler needs to be generated with the
  correct DB2 access properties

40
REVIEW Specifying Connection Info to
Databases
Your EGL/JSF Web Page

• For Web Applications (J2EE)
• JNDI Name in build descriptor
• JNDI reference in web.xml
• Project EAR File
• Application Deployment Descriptor

Step 5
Steps 6, 7
Step 4
Steps 1, 2 3
Note that you will have a new JNDI Name for your
DB2 access in this lab these screen
captures show your current JNDI Name
41
? 1. Add a new JAAS Authentication Entry

• From Project Explorer
• Expand EGLWebEar
• Open the Deployment tab
• Scroll down and Add a new JAAS Authentication
  Entry specifying your DB2
• User ID
• Password

42
? 2 Create a new JDBC Provider

• Continuing on the Deployment tab
• Scroll up and from Data Sources, add a new JDBC
  Provider
• Under JDBC Provider List, click Add
• Select
• Database type
• IBM DB2
• JDBC provider type
• DB2 Universal JDBC Driver Provider
• From Create JDBC Provider
• Select and Remove the existing Class path
  external JARs
• Click Add External JARs and from your DB2
  installation directory, browse to the \java\
  subdirectory and select the three JAR files shown
  below

43
? 3. Create a new JNDI Name for your DB2 Database
1 of 2

• Continuing on the Deployment tab - Select your
  new JDBC
• From Data source defined in the JDBC provider
  selected above, click Add
• From Create Data Source, select
• DB2 Universal JDBC Provider
• Click Next gt
• From Create Data Source
• Add these entries ?
• Name
• JNDI Name
• (lower-case)
• Use the combo-box
• to select DB2Auth for
• the Component and

44
? 3. Create a new JNDI Name for your DB2 Database
2 of 2

• From Create Resource Properties, specify the
  following three property values
• databaseName
• Enter SAMPLE
• - if you are using the Sample DB2 database on
  your PC
• Enter your actual DB2 database name
• If you are accessing a remote DB2 database
• Example REDBK1
• 2. serverName
• Enter localhost
• If you are using DB2 on your PC
• Enter the IP Address of your DB2 Server
• If you are accessing Remote DB2
• Ex. carmvs1.pok.ibm
• 3. portNumber
• - which defaults to 50000 for localhost DB2
• - you will need the actual port for your system
  if you are accessing remote DB2

45
? 4. Add a New SQL Resource Reference to your
Projects web.xml file

• Add an entry in web.xml for the new JNDI name
• From Project Explorer, expand
  \WebContents\WEB-INF\ and open web.xml
• From the References tab, click the Add button
• Add a ? Resource reference
• Click Next gt
• Enter the settings shown here
  ?
• Name jdbc/sample
• Type Open the combo-box. Scroll to the bottom
  and select
• javax.sql.DataSource
• Authentication Open the combo-box and select
  Container

46
? 5. Create a new EGL Build File for your DB2
Connection

• As you did in a previous lab in this course, you
  will create a new Build File, and then modify
  its entry so that it references your DB2 JNDI
  name. From Project Explorer
• Right-Click over \EGLSource\ and select
• New, Other, expand EGL, and select EGL Build File
• Name the file DB2Build and close your new build
  file
• ? Copy the contents of EGLWeb Build File entries
  into DB2Build
• Right-Click over EGLWeb.eglbld and select Open
  with Text Editor
• Click anywhere inside the file, and press
• Ctrl/A - to select all text
• Ctrl/C - to copy the selected text
• Close EGLWeb.eglbld
• Right-Click over DB2Build.eglbld and select Open
  with Text Editor
• Click anywhere inside the file, and press Ctrl/V
  - to paste the copied text
• 1. Change the sqlJNDIName value
• 2. Be sure that J2EEYES

J2EE"YES"
"jdbc/sample"
47
? 6. Create a new Page 1 of 3

• From Project Explorer, Right-click over
  \WebContents\ and create a new Web Page, named
  testDB2Page.jsp
• Create it using a template from the MyTemplates
  folder
• Modify the default page title text
• Right-click over the page in the content area
• Select Edit Page Code
• Select all of the existing
  JSFHandler
  boiler-plate
  statements and replace them
  
  with the code in the Notes
  section of this
  slide
• Save (Ctrl/S) your code
• There shouldnt be anything
• new in this code that you
• havent seen before in other
• sections of this course.
• we hope ?

48
? 6. Create a new Page 2 of 3

• From Page Designer, from the Page Data area
• Select empArray drag and drop it onto the page
• From Configure Data controls, select ?
  Displaying an existing record (read/only)
• Select just the seven fields shown below
• With the control selected, access the Properties
  tab
• Specify Border 1

49
? (Optional) 6. Create a new Page 3 of 3

• From Page Designer, with the entire dataTable
  selected (see Notes) from Properties
• Select the Display options sub-tab
• Delete the existing Column classes value
• Click Add column to categorize table rows
• This will add a new column to the left side of
  the dataTable for data grouping
• With your new column selected
• From the new columns Value Click the browse
  button
• Expand empArray and select DEPTNAME
• This specifies that the dataTable rows should be
  aggregated (grouped by) DEPTNAME

50
? 7. Specify the New Build File for Your Web Page

• ? Before running your page, you must assign the
  JSFHandler to your new Build File in order for
  the EGL generation to pick up the proper JNDI
  Name to access the DB2 Sample database.
• From Project Explorer
• Expand \EGLSource\jsfhandlers\
• Right-click over testDB2Page.egl and select
  Properties
• From Properties, select EGL Default Build
  Descriptor
• For both the Target system and Debug build
  descriptors, open the combo-box and select
• EGLWebWebBuildOptions ltEGLWeb/EGLsource/DB2Build.
  eglbldgt

51
? Run the Page

• Right-click over the content area, and select
  Run on server
• Click the triangle (twisties) to expand/collapse
  Departments

Click to expand ?