Access Methods

Content

DVM for z/OS technology provides multiple methods for client applications to access data. These access methods allow external applications to seamlessly make requests for READ or WRITE operations of underlying data, regardless of location or format, shown in Figure 1.
 

DatabaseMetaData databaseMetadata = conn.getMetaData();
String catalog = conn.getCatalog(); String schemaPattern = null;
String tableNamePattern = "%";
String[] types = null;
ResultSet tables = databaseMetadata.getTables(catalog, schemaPattern, tableNamePattern, types;

• tableNamePattern is used to filter objects based on their name
• types can be used to filter objects based on their type, such as TABLE objects String[] types = {"TABLE"};

DatabaseMetaData databaseMetadata = conn.getMetaData();
String catalog = conn.getCatalog();
String schemaPattern = null;
String tableNamePattern = "%";
String colNamePattern = "%";
ResultSet tables = databaseMetadata.getColumns(catalog, schemaPattern, tableNamePattern, colNamePattern;

• tableNamePattern can be used to filter objects based on their name
• colNamePattern can be used to filter columns based on their name

• dv-jdbc-[version #].jar: DVM JDBC driver core implementation file
• log4j-api-[version #].jar: The logging framework API file
• log4j-core-[version #].jar: The logging framework implementation file.
• log4j2.xml: A sample logging configuration file.

1. Locate the first DFHDELIM entry in the PLTP1 list
   DFHPLT TYPE=ENTRY, PROGRAM=DFHDELIM
2. Insert the AVZXMTRI list as the second entry for the DS Client task-related user exit
   DFHPLT TYPE=ENTRY, PROGRAM=AVZXMTRI
3. Run your CICS assembly job

1. For each CICS region, modify and submit the AVZCICSD job that is in hlq.SAVZCNTL data set
2. Update LIST(YOURLIST) to match the start-up group list for the CICS region

1. Open a connection to the DVM Server
2. Send the SQL command to the DVM Server
3. Receive the complete query results from the issues SQL statement
4. Close the connection to the DVM Server

//SYSLIB DD DSN=DVM.SAVZLOAD,DISP=SHR
//SYSLMOD DD DSN=APPL.LOADLIB,DISP=SHR
//SYSIN DD * 
INCLUDE SYSLIB(AVZCLIEN)
NAME module (R)

STRING
"SELECT STAFFVS_KEY_ID,
"DELIMITED BY SIZE" STAFFVS_DATA_NAME,
"DELIMITED BY SIZE" STAFFVS_DATA_DEPT,
"DELIMITED BY SIZE" STAFFVS_DATA_JOB,
"DELIMITED BY SIZE" STAFFVS_DATA_YRS,
"DELIMITED BY SIZE" FROM STAFFVS,
"DELIMITED BY SIZE" INTO SQL-TEXT.

SELECT STAFFVS_KEY_ID, STAFFVS_DATA_NAME, STAFFVS_DATA_DEPT, STAFFVS_DATA_JOB, STAFFVS_DATA_YRS 
FROM STAFFVS;

• Space units: TRACK
• Primary Quantity:  30
• Secondary Quantity: 2
• Directory blocks: 15
• Record format: U
• Record length: 0
• Block size: 32760
• Data set name type: LIBRARY

//AVZRPCLB DD DISP=SHR,DSN=&HLQ..SAVZRPC
// DD DISP=SHR,DSN=

/*------------------------------------------*/
/* Enable z/OS Connect interface facility */
/*------------------------------------------*/
if DontDoThis then do

"DEFINE ZCPATH",
" NAME(ZCNALL)",
" RNAME(DVJR1)",
" WNAME(DVJG1)"

• A generic or discrete CBIND resource definition is required. A generic definition uses a CBIND class profile of BBG.WOLA.<WolaGroup>.** with UACC(READ), whereas a discrete definition uses a CBIND class profile of BBG.WOLA.<WolaGroup>.<WolaName2>.<WolaName3> with UACC(READ).
• The WolaGroup is the WNAME specified in the DEFINED ZCPATH command, WolaName2 and WolaName3 are the values defined in the ZCONNECTPWNAMEX parameter. If ZCONNECTPWNAMEX has not been defined, the WolaName2 value must be NAME2 and the WolaName3 value must be NAME3.

• Transfer the member hlq.SAVZBIN(AVZBIN4) to your workstation in binary mode
• Rename the file to com.rs.dv.zosconnect.provider.feature_1.0.0.esa
• Copy the file to a UNIX System Services (USS) directory  Connect to UNIX System Services (USS)
• Change directory to //wlp/bin, where is the path directory for your z/OS Connect EE installation. For example, change to the following directory:
  cd /usr/lpp/zosconnect/v330/wlp/bin
• Set JAVA_HOME = environment variable to the path of your 64-bit IBM Java SDK:
  export JAVA_HOME=/usr/lpp/java/IBM/J8.0_64
• Set the WLP_USER_DIR environment variable to the location where your server instances and user features are stored.
  export WLP_USER_DIR=/var/zosconnect
• ./installUtility install /dvmServiceProvider/com.rs.dv.zosconnect.provider.feature_1.0.0.esa
• The zCEE server.xml can be edited using IBM Explorer for z/OS
  • Under xml element, add the following lines:
    
     

    <feature>usr:dvsProvider</features>
    <feature>zosLocalAdapters-1.0</feature>

    Where usr:dvsProvider is DVM Service Provider on zCEE. zosLocalAdapters-1.0 is the WOLA provider used to communicate between the DVM Service Provider on zCEE and DVM Server.
• Under the <server> xml element, add entries for the required DVM Service Provider on zCEE:
  
   

  <zosconnect_zosConnectService
    id="zosConnectDvsService"
    serviceName="DvsService"
    serviceRef="dvsService"
    serviceDescription="IBM DV Service provider"
    invokeURI="/dvs" />
  
  <usr_dvsService
    id="dvsService"
    connectionFactoryRef="wolaCF"
    registerName="DVJR1"
    serviceName="DVJS1"
    invokeURI="/dvs" />

  Where:
  • serviceRef in <zosconnect_zosConnectService> must be the same as id in
  • invokeURI in <zosconnect_zosConnectService> must be the same as invokeURI in <usr_dvsService>. It represents the "root" directory of DVM Web services: it means that all DVM Web services are reachable with an URL, which starts with http(s)://<zos_connect_ip>:<zos_connect_port>/dvs/…
  • connectionFactoryRef in <usr_dvsService> must have the same value as connectionFactoryRef in <zosconnect_localAdaptersConnectService> and id in <connectionFactory> both defined in the next step
  • registerName in <usr_dvsService> is the same as RNAME defined in DEFINE ZCPATH command in the DVM IN00 configuration member.
  • serviceName in <usr_dvsService> must have the same value as serviceName in <zosconnect_localAdaptersConnectService> defined in the next step.
• Under <server> xml element, add the following entries required for the WOLA Service Providers
  
   

  <zosLocalAdapters
      	wolaGroup="DVJG1"
      	wolaName2="NAME2"
      	wolaName3="NAME3" />
      
  <connectionFactory id="wolaCF" jndiName="eis/ola">
      	<properties.ola />
   </connectionFactory>
      
   <zosconnect_zosConnectService
      	id="sdef1"
      	serviceName="dvs1"
      	serviceAsyncRequestTimeout="600s"
      	serviceRef="svc1" />
      	
   <zosconnect_localAdaptersConnectService
      	id="svc1"
          registerName="DVJR1"
      	serviceName="DVJS1"
      	      connectionFactoryRef="wolaCF"
      	      connectionWaitTimeout="7200" />
  

  Where:
  
  a. wolaGroup in <zosLocalAdapters> has the same value as WNAME defined in:
      DEFINE ZCPATH command in the DVM IN00 configuration member
  b. wolaName2 and wolaName3 have the same value as WNAME defined in the ZCONNECTPWNAMEX parameter in the DVM IN00 configuration       
      member.  If ZCONNECTPWNAMEX has not been defined in DVM IN00 configuration member, WolaName2 value must be NAME2 and WolaName3
      value must be NAME3.
  c. id in <connectionFactory> is the same as connectionFactoryRef in <zosconnect_localAdaptersConnectService> and <usr_dvsService>
  d. serviceRef in <zosconnect_zosConnectService> must have the same value as id in <zosconnect_localAdaptersConnectService>
  e. registerName in <zosconnect_localAdaptersConnectService> is the same as RNAME defined in DEFINE ZCPATH command in the DVM IN00
      configuration member
  f. serviceName in <zosconnect_localAdaptersConnectService> must have the same value as serviceName in <usr_dvsService> defined in the
     previous step.

  

SELECT SMF30JBN AS JOB_NAME, SMF30STM AS STEP_NAME, SMF30PGM AS PROGRAM, SMF_SSI AS TYPE, SMF30CSU AS CPU
FROM ( SELECT SMF_STY, SMF30HPT, SMF30IIP, SMF30RCT, SMF30HPT, SMF30JQT, SMF30RQT,SMF30HQT, SMF30SQT, SMF30STI, SMF_TIME,
SMF30SIT, SMF30IO, SMF30PSN,SMF30MSO, SMF30SRB, SMF30CSU, SMF30SRV, SMF30ISB, SMF30ICU, SMF30PGM,SMF30CL8, SMF30SSN,
SMF30JBN, SMF30JNM, SMF30STM, SMF30STN, SMF_SSI,SMF_SID, SMF30CPT, SMF30CPS, SMF30CPT, SMF30CPS,SMF30_TIME_ON_IFA,
SMF30_TIME_ON_SUP, SMF30TCN, SMF30TEP, SMF30SCC, LS_TIMESTAMP_LOCAL FROM SMF_03000_SMF30IDA INNER JOIN
SMF_03000_SMF30CAS B ON A.BASE_KEY = B.BASE_KEY INNER JOIN SMF_03000 C ON A.BASE_KEY = C.BASE_KEY INNER JOIN
SMF_03000_SMF30CMP D ON A.BASE_KEY = D.BASE_KEY INNER JOIN SMF_03000_SMF30URA E ON A.BASE_KEY = E.BASE_KEY INNER JOIN SMF_03000_SMF30PRF F ON A.BASE_KEY = F.BASE_KEY ) WHERE SMF_STY = 4;

• Use Case 1: TSO or SPUFI
  QMF for TSO or SPUFI can query the DVM data with three-part names.
• Use Case 2: Cobol applications
  Custom Cobol programs may be used to pull data using embedded SQL with three-part names (or View or Alias). However, when the packages for the SQL are bound, they must also be bound in the DVM Server instance associated with IDF. The same situation faced with three-part names accessing another remote Db2 for the z/OS subsystem. The job that compiles the program has a step to either perform a remote bind or to copy the packages to the instance of DVM associated with IDF.
• Use Case 3: Db2 Stored Procedures
  Db2 Stored Procedures can be created that run queries aimed at DVM data sources using IDF. SQL Call statements to these Db2 Stored Procedures can then be incorporated into DB2 Restful Services.

• AVZUDT9N for the z196 system
• AVZUDTAN for the zEC12 system
• AVZUDTBN for the z13 system
• AVZUDTCN for the z14 system
• AVZUDTDN for the z15 system

SELECT TEMPID, NAME, ADDRESS, EDLEVEL,COMMENTS
FROM TWSHAWN.AVZW_VSASAPPLICANT
WHERE TEMPID >450

SELECT TEMPID,NAME,ADDRESS,EDLEVEL,COMMENTS
FROM TABLE (TWSHAWN.AVZW_SASAPPLICANT ('WHERE TEMPID >450', 'AVZW...SASAPPLICANT', '5,TEMPID,NAME,ADDRESS,EDLEVEL,COMMENTS', ''))

1. Connect to the database that requires access to DVM Server from either the command line or using the Data Studio Manager (DSM), Data Management Console (DSM), Unified Consoles as part of Db2 Warehouse on-prem and Cloud offerings, and the IIAS system.
2. Create a connection server pointing to the DVM Server along with all other options specified in the following command, and as well as in Figure 23.

db2 "CREATE SERVER <server_name> TYPE JDBC WRAPPER JDBC OPTIONS
(DRIVER_PACKAGE '<path to DVM jdbc driver>',
DRIVER_CLASS 'com.rs.jdbc.dv.DvDriver',
URL 'jdbc:rs:dv://<dvm_ip_address>:<dvm_port>;DBTY=DVS;SUBSYS=NONE;
PMDSQL=true',
pushdown 'Y',
DB2_MAXIMAL_PUSHDOWN 'Y',
db2_varchar_blankpadded_comparison 'Y',
db2_char_blankpadded_comparison 'Y',
collating_sequence 'Y' )"

• pushdown ‘Y’: the federated server considers letting DVM evaluate operations. If you set PUSHDOWN to N, the federated server retrieves select columns from the remote data source and doesn't let the DVM server evaluate other operations, such as joins
• DB2_MAXIMAL_PUSHDOWN ‘Y’: the federated server pushes as many parts of the query as possible to DVM for processing
• db2_varchar_blankpadded_comparison 'Y’ and db2_char_blankpadded_comparison ‘Y’: the federated server pushes filtering based on character columns to DVM Server
• collating_sequence 'Y’: character/numeric data predicates comparison, character range predicates comparison, and sort operations might be pushed down if collating sequences are the same

db2 "CREATE USER MAPPING FOR <user> SERVER <server_name> OPTIONS (REMOTE_AUTHID 'XXX', REMOTE_PASSWORD 'xxx')"

db2 "CREATE NICKNAME <nickname> FOR <server_name>.<virtual table/view>"