How to setup CA Pan/SQL to access DB2 data on AIX
Article ID: 27268
Updated On: 10-12-2023
Products
Issue/Introduction
Setup Instructions for accessing DB2 data on AIX
Environment
Release: PSQLOR00200-2.4-Pan/SQL-for Oracle
Component: CA Pan/SQL 2.4C
Cause
Background:
Most of the following setup steps can be run independently of the others. This can be assumed, unless otherwise noted.
If the VWOWNER value/name is changed from the default PANSQL24, it will need to be changed in both the AIX and z/OS views.
Resolution
On the AIX box /machine all that is required is the creation of 5 views.
Please note these are different than the ones included in the install for z/OS.
They are provided at the bottom of this article. Make a note of the VWOWNER if changing it from the default (PANSQL24).
This will need to match the VWOWNER of the views created on z/OS.
The DB2 setup on z/OS requires three inserts into the "CDB" (communications database) in the catalog.
These inserts must be done in the order listed. At the minimum, sysibm.locations must have the location name added.
Since most AIX systems have the SAMPLE database installed, we will use it in our example.
INSERT INTO SYSIBM.LOCATIONS
(LOCATION, LINKNAME, IBMREQD, PORT)
VALUES ('SAMPLE', 'LIAUS533', 'N', '50081')
Second, the IPNAMEs table must include
INSERT INTO SYSIBM.IPNAMES
(LINKNAME, SECURITY_OUT, USERNAMES, IBMREQD, IPADDR)
VALUES ('LIAUS533', 'P', 'O', 'N', 'LIAUS533.CA.COM')
Third, sysibm.usernames
INSERT INTO SYSIBM.USERNAMES
(TYPE, AUTHID, LINKNAME, NEWAUTHID, PASSWORD, IBMREQD)
VALUES('O', 'TESTID', 'LIAUS533', 'NTESTID', 'TESTPW', 'N')
For details about other options available, see IBM's DB2 UDB for z/OS manuals.
In order for these changes to take effect, the DDF will need to be restarted. Consult the DB2 Administrator before doing this.
On the Command line type:
-STOP DDF
-START DDF
The following changes need to be made in the CA Pan/SQL CAIMAC members before the IJ3STGE1 job is run.
In member DQBNDPLN:
If this job will also be used to maintain the install for accessing the data on z/OS, an optional suggestion is to create a member called DQBNDPAC and edit and use it to access the data on AIX.
Add two Bind packages and a Pklist to this member.
BIND PACKAGE (SAMPLE.PAN) MEMBER(DQPS024)
BIND PACKAGE (PAN) MEMBER(DQPS024)
BIND PLAN (DQPS024) PKLIST (*.PAN.*)
If running DB2 v8 on z/OS, add DEFER (PREPARE) for each Bind. If the plan name is changed from the default DQPS024, make a note of the new name. It will also need to be changed in a couple other places. If a member name other than DQSP024 is used, make a note of that also.
Replace DQCTGVW2 with the one supplied below.
In DQGRANT add the names of the two packages. If the plan name is different, change it here and in OMSMCMD2.
In DQSMCMD, the ENV=MVS parameter must be changed to AIX.
The last two jobs (IJ3STGE1 and CB2ACMDL) are found in the CA Pan/SQL highlevelqual.INSTALL.JCL dataset.
First, in IJ3STGE1, change DQCTGVWS to DQCTGVW2.
If the DQBNDPAC member was created as a suggestion above, change DQBNDPLN to DQBNDPAC.
If a member name other than DQPS024 was used in the bind above, change DBRM member to that name. Otherwise, a "member xxx not found in pds search order" error will occur.
Run IJ3STGE1. If it completes with a RC=4 or lower, run CB2ACMDL. If that completes with a RC=4 or lower, the installation is complete.
Add PARM ('/location') to your Easytrieve program.
PARM ('/SAMPLE')
Edit tablenames by adding the qualifier SAMPLE. (This is not required for the Include statement.)
SELECT column +
FROM SAMPLE.mytable
======================================================================
Views for AIX and new CAIMAC member DQCTGVW2
DROP VIEW PANSQL24.DQUSERID;
DROP VIEW PANSQL24.DQTBLECOLS;
DROP VIEW PANSQL24.DQUSERTPRV;
DROP VIEW PANSQL24.DQSYNONYMS;
DROP VIEW PANSQL24.DQALIAS;
CREATE VIEW PANSQL24.DQUSERID
(USERID)
AS SELECT
SYSIBM.SYSTABLES.CREATOR
FROM
SYSIBM.SYSTABLES;
CREATE VIEW PANSQL24.DQTBLECOLS
(TBLOWNER,
TBLNAME,
COLNAME,
DATATYPE,
UNIQUEVALUES,
COLPOS,
DATALEN,
SCALE,
NULLS,
LABEL)
AS SELECT
SYSIBM.SYSCOLUMNS.TBCREATOR,
SYSIBM.SYSCOLUMNS.TBNAME,
SYSIBM.SYSCOLUMNS.NAME,
SYSIBM.SYSCOLUMNS.COLTYPE,
SYSIBM.SYSCOLUMNS.COLCARD,
SYSIBM.SYSCOLUMNS.COLNO,
SYSIBM.SYSCOLUMNS.LENGTH,
SYSIBM.SYSCOLUMNS.SCALE,
SYSIBM.SYSCOLUMNS.NULLS,
' '
FROM
SYSIBM.SYSCOLUMNS;
CREATE VIEW PANSQL24.DQUSERTPRV
(GRANTEE,
TBLOWNER,
TBLNAME,
GRANTOR,
UPDATECOLS,
SELECTPRIV,
INSERTPRIV,
DELETEPRIV,
UPDATEPRIV,
TBLTYPE,
TBLDESCR)
AS SELECT
SYSIBM.SYSTABAUTH.GRANTEE,
SYSIBM.SYSTABAUTH.TCREATOR,
SYSIBM.SYSTABAUTH.TTNAME,
SYSIBM.SYSTABAUTH.GRANTOR,
CASE WHEN SYSIBM.SYSCOLAUTH.COLNAME IS NULL THEN ' ' ELSE '*' END UPDATECOLS,
SYSIBM.SYSTABAUTH.SELECTAUTH,
SYSIBM.SYSTABAUTH.INSERTAUTH,
SYSIBM.SYSTABAUTH.DELETEAUTH,
SYSIBM.SYSTABAUTH.UPDATEAUTH,
SYSIBM.SYSTABLES.TYPE,
SYSIBM.SYSTABLES.REMARKS
FROM
SYSIBM.SYSTABAUTH,
SYSIBM.SYSTABLES,
SYSIBM.SYSCOLAUTH
WHERE
SYSIBM.SYSTABAUTH.TCREATOR = SYSIBM.SYSTABLES.CREATOR
AND
SYSIBM.SYSTABAUTH.TTNAME = SYSIBM.SYSTABLES.NAME
AND
SYSIBM.SYSCOLAUTH.TNAME = SYSIBM.SYSTABLES.NAME
AND
SYSIBM.SYSCOLAUTH.CREATOR = SYSIBM.SYSTABLES.CREATOR;
CREATE VIEW PANSQL24.DQSYNONYMS
(USERID,
ALTNAME,
TBLOWNER,
TBLNAME)
AS SELECT
SYSIBM.SYSTABLES.CREATOR,
SYSIBM.SYSTABLES.NAME,
SYSIBM.SYSTABLES.BASE_SCHEMA,
SYSIBM.SYSTABLES.BASE_NAME
FROM
SYSIBM.SYSTABLES
WHERE SYSIBM.SYSTABLES.TYPE = 'S';
CREATE VIEW PANSQL24.DQALIAS
(OWNER,
ALIASNAME,
TYPE,
TBLOWNER,
TBLNAME)
AS SELECT
SYSIBM.SYSTABLES.CREATOR,
SYSIBM.SYSTABLES.NAME,
SYSIBM.SYSTABLES.TYPE,
SYSIBM.SYSTABLES.BASE_SCHEMA,
SYSIBM.SYSTABLES.BASE_NAME
FROM
SYSIBM.SYSTABLES
WHERE SYSIBM.SYSTABLES.TYPE = 'A';
GRANT SELECT ON PANSQL24.DQUSERID TO PUBLIC;
GRANT SELECT ON PANSQL24.DQTBLECOLS TO PUBLIC;
GRANT SELECT ON PANSQL24.DQUSERTPRV TO PUBLIC;
GRANT SELECT ON PANSQL24.DQSYNONYMS TO PUBLIC;
GRANT SELECT ON PANSQL24.DQALIAS TO PUBLIC;
Feedback
