How do I setup CA Pan/SQL to access DB2 data on AIX?

Document ID : KB000027268
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction/Summary: 

Setup Instructions for accessing DB2 data on AIX

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.

Environment:  

CA Pan/SQL 2.4C

Instructions: 

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" (commumications 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;