Connecting to z/OS data sources

Content

           

"DEFINE DATABASE TYPE(type_selection)", 
"NAME(name)" , 
"LOCATION(location)" , 
"DDFSTATUS(ENABLE)" , 
"DOMAIN(your.domain.name)" , 
"PORT(port)" , 
"IPADDR(1.1.1.1)" , 
"CCSID(37)" , 
"APPLNAME(DSN1LU)" , 
"IDLETIME(110)"

• Software AG's Adabas SQL requires an off-host server (CONNX) with limits for scale, performance, and failover that can be addressed with DVM server technology
• The DVM server can service both Adabas and Natural programs and serve as a substitute for Software AG's EntireX solution
• DVM for z/OS is an integral component for IBM's IDAA Loader component for loading Adabas data to IBM's Data Analytics Accelerator (IDAA)
• DVM for z/OS DSclient supports both DRDA and the JDBC Gateway server. This offers a replacement option to Software AG's Natural SQL Gateway
• Organizations with Db2 for z/OS can drive transaction activity through their Db2 subsystem using the Integrated DRDA Facility (IDF) using Db2 as a Data Hub. This option works to isolate workloads to a single system.
• DVM for z/OS integrates with AT-TLS and improves configuring secure access to Adabas data
• DVM for z/OS helps to offload SORT, JOIN, and data translation operations

    

• Adabas by design has field names that are two characters in length and it's recommended to use longer field names
• Adabas DBAs can generate Natural DDM views
• When flattening ME/PU arrays limit the length of the field name to 26 bytes to account for additional bytes used during data mapping

• Hierarchical direct access method (HDAM) - VSAM and OSAM
• Hierarchical indexed direct access method (HIDAM) - VSAM and OSAM
• Partitioned HDAM (PHDAM) - VSAM and OSAM
• Partitioned HIDAM (PHIDAM) - VSAM and OSAM
• Fast Path data entry database (DEDB)

DoThis = 1 
DontDoThis = 0 
... 

/* Enable IMS CCTL/DBCTL support */ 
if DontDoThis 
then 
do MODIFY PARM NAME(DBCTL) VALUE(YES)" 
... 

• Virtual Storage Access Method (VSAM) is an IBM DASD file storage access method, used to store mission-critical data and managed by a file management system. The problem is that modern applications are looking to access this data more readily, but VSAM is not a database, therefore lacks many of the standard characteristics for data integrity, structure, access, and data protection.
• Sequential files have records in the file in the order they are written and can only be read back in the same order. They are a simple form of a COBOL file. Every record in a relative file can be accessed directly without having to read through the other records. Once virtualized, the DVM server allows this sequential data to be retrieved in any group order, along with query predicates.
• Delimited files are typically not found in a mainframe environment, but can be in any file storage system and have column delimiters for a stream of records that consist of fields that are ordered by column. Each record contains fields for one row, with individual fields separated by column delimiters. The DVM server requires the use of Rules, to virtualize delimited files.
• System Management Facility (SMF) offers a way of keeping track of what is happening on your mainframe. There are 255 SMF records available to help deliver information about all functions and products running on the z/OS environment from the machine level to application-specific activity related to processing, I/O, allocated memory, and so on.
• System level files (Log stream, Syslog, Operlog, and so on.)
  • Syslog is a standard for computer message logging and can be used for computer system management, security auditing, analysis, and debugging messages.
  • Log stream is an application-specific collection of data that oftentimes organizations use to optimize their environment and routinely store in the form of SMF records. Log stream datasets are VSAM linear datasets.
  • Operations log (OPERLOG) is a sysplex-wide log of system messages residing in a system logger log stream, similar to Syslog.

• VSAM cluster representing the VSAM dataset, including one or both of data or index data records. The DVM server requires the name of a valid VSAM cluster as part of the source library for mapping purposes. There are no more configuration steps required for the DVM Server to set up the SQL interface to native VSAM files.
• The dataset Member name contained in the virtual source library that maps to the copybook (record layout) for the underlying data structure residing on disk

-- Description: Retrieve the result set for VSAM_TABLE (up to 1000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS/Virtual Tables/VSAM_TABLE
-- Remarks: VSAM - FBOR.STAFF.VSAM
SELECT * FROM VSAM)_TABLE LIMIT 1000;

1. OPERLOG_SYSLOG accesses the SYSPLEX logstream and is defined in the global variable GLOBAL2.SYSLOG.DEFAULT after the  AVZSYSLG rule is enabled
2. OPERLOG_MDB
3. OPERLOG_MDB_MDB_CONTROL_OBJECT
4. OPERLOG_MDB_MDB_TEXT_OBJECT
5. SYSLOG

• AVZSYSLG uses a global variable to specify the name of the dataset to use for the SYSLOG data.
• AVZSYSL2 supports the use of generation data group (GDG) dataset names. One of the following formats is expected and the general use of global variables allows for maximum flexibility in the overall configuration.
• SYSLOG_GDG_nnnn, where nnnn is a relative GDG number (0 - 9999) that is appended to the GDG base name value that is obtained from the GLOBAL2.SYSLOG.GDGBASE variable.
• For example, if the table name as specified in the SQL statement is SYSLOG_GDG_1, then the dataset name returned by this rule is HLQ.SYSLOG(-1), depending on the value in GLOBAL2.SYSLOG.GDGBASE.
• SYSLOG_DSN_suffix, where suffix is used as the last part of a global variable of the form GLOBAL2.SYSLOG.suffix to look up the name of the dataset to be used. If this variable does not exist, the dataset name is specified in GLOBAL2.SYSLOG.DEFAULT is used to read the SYSLOG records.
• Global variable examples possible for use with this rule:
  
  GDGBASE       hlq.SYSLOG
  DEFAULT         hlq.SYSLOG(0)
  TODAY             hlq.SYSLOG(0)
  YESTERDAY   hlq.SYSLOG(-1)

-- Description: Retrieve the result set for SYSLOG (up to 1000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS/Virtual Tables/SYSLOG
-- Remarks: HTTP://10.3.58.61:1201
SELECT * FROM SYSLOG LIMIT 1000;

 

SELECT * FROM OPERLOG_SYSLOG LIMIT 1000;

SELECT * FROM OPERLOG_SYSLOG
WHERE SYSLOG_JOBID='AVZS';

-- Description: Retrieve the result set for OPERLOG_SYSLOG (up to 1000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS/Virtual Tables/OPERLOG_SYSLOG
-- Remarks: Logstream - SYSPLEX.OPERLOG
SELECT * FROM OPERLOG_SYSLOG WHERE SYSLOG_JOBID = 'AVZS';

• vtb.optbdlco sets the column delimiter (default value is the comma ',')
• vtb.optbdlch sets the delimiter (default is the double quotation mark ")
• vtb.optbdlhr identifies and removes the header record containing column names. If specified without a header prefix, the system compares the first token in each line to the first column name in the table to recognize and discard the header. The default is no header checking with value 0.
• vtb.optbdlhp is a global parameter that defines prefix data that identifies the beginning of a header line to be discarded. The specified value can contain a maximum of 32 bytes. This value is compared to the beginning of each delimited line of data before any tokenization is performed.

-- Description: Retrieve the result set for CLIENT_INFO_DELIMITED (up to 10000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS/Virtual Tables/CLIENT_INFO_DELIMITED
-- Remarks: zFS file - /u/arnould/CLINET_INFO/DELIMITED.csv
SELECT * FROM MDDLM_CLIENT_INFO_DELIMITED LIMIT 10000;

• APAR OA49263 provides real-time SMF support and is a requirement for the configuration of real-time SMF data access.
• APAR OA48933 is required to address accessing log streams. SMF log stream configuration is required for in-memory resource support. In this section, we are going to cover how to access this information from the DVM server.

• SMF datasets - SMF information is recorded in MANx datasets. When a dataset gets full, the data is processed using IFASMFDP. The output of IFASMFDP is required when using global variables.
• Log streams - SMF information can be recorded in multiple log streams and determined by the dataset name beginning with IFASMF, which is used by the VTB rule for SMF.
• In-memory SMF data offers real-time access and can be read directly from the z/OS system buffer.

IF DoThis 
THEN DO 
"DEFINE SMF NAME(IFASMF.INMEM)", 
"STREAM(IFASMF.ZB01.INMEM)", 
"BUFSIZE(500)", 
"TIME(0)" 
END 

• "rname" is the name of the in-memory resource.
• RESSIZMAX defines the size of the buffer available for this in-memory resource, in megabytes or gigabytes.
• TYPE defines the SMF record types that are to be recorded to this in-memory resource.
• NOTYPE directs SMF is to collect all SMF record types, except record types explicitly specified.

1. Change Global Prefix to GLOBAL2.
2. Select SMFTBL2, as shown in Figure 36.
   
   
   Figure 36 - Editing SMFTBL2 VTB rule
3. Configure the SMF data access option for IN-MEMORY by adding the following Global Variable to the existing ones by typing at the command lines. 
   
   S IM = "IFASMF.INMEM"
   S IM2 = "IFASMF.INMEM.Db2"
4. You should see the new Global Variables in the list:
   
   GLOBAL2.SMFTBL2.IM = "IFASMF.INMEM"
   GLOBAL2.SMFTBL2.IM2 = "IFASMF.INMEM.Db2"
   
   Pro tip: Be careful when defining the dataset name. You must enter the name in the upper case. If you specify the correct name, but in lower or mixed cases, the allocation fails.
5. Submit the following SQL Query to display SMF30 records (which are collected), from the IFASMF.INMEM buffer, as shown in Figure 37 displays records that are captured in real-time through in-memory SMF collection. The SQL syntax for Virtual Tables is composed of the Table Mapping (SMF_03000) in conjunction with IM (IFASMF.INMEM) separated by a 'single' underscore "_".
    

   SELECT * FROM SMF_03000_IM LIMIT 10000;

 

• SMF_07001
• SMF_07001_SMF70AID
• SMF_07001_SMF70BCT

SELECT SMF_TIME, SMF_SID, SMF_SEQN, SMF70VPA, SMF70BPS, 
FROM SMF_07001 A0 JOIN SMF_07001_SMF70BPD A9 
ON A0.CHILD_KEY = A9.PARENT_KEY; 

-- Description:  Retrieve the result set for DW01_DSN81210_EMP (up to 10000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS?Virtual Tables?DW01_DSN81210_EMP
-- Remarks: DRDA - DSN81210.EMP
SELECT * FROM DW01_DSN81210_EMP_ LIMIT 10000;

-- Description:  Retrieve the result set for DW01_DSN81210_EMP (up to 10000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS?Virtual Tables?DW01_DSN81210_EMP
-- Remarks: DRDA - DSN81210.EMP
SELECT * FROM MDLDU_DW01_DSN81210_EMP_ARNOULD_DW00_UNLD_DSN8D12A_DSN8S12E;

1. Extracts the table name DW01_DSN81210_EMP and sets the VTB global variable vtb.optbmtna.
2. Extracts the dataset name ARNOULD_DW00_UNLD_DSN8D12A_DSN8S12E, converts the underscores to periods, and sets the VTB global variable vtb.optbdsna.

1. Customize the Data Virtualization Manager configuration member (AVZSIN00) to enable virtual table rule events by configuring the SEFVTBEVENTS parameter in the member, as presented in Figure 45 and as follows: "MODIFY PARM NAME(SEFVTBEVENTS) VALUE(YES)"
   
   
   Figure 45 - Activating SEFVTBEVENTS in AVZSIN00 member
2. Access the VTB rules, as follows:
   1. In the Data Virtualization Manager server - Primary Option Menu, specify option E, Rules Mgmt.
   2. Specify option 2, SEF Rule Management.
   3. Enter VTB for Display Only the Ruleset Named, like in Figure 46.
      
      \
      
      Figure 46 - Displaying VTB Rules only 
3. Customize the AVZMDLDU rule, as follows:
   1. Specify S next to AVZMDLDU to edit the rule, like in Figure 47
      
      Figure 47. Editing VTB rule AVZMDLDU
   2. Find the vtb.optbdsna variable and specify the name of the Db2 unload dataset to process, as presented in Figure 48.
      
      
      Figure 48 - vtb.optbdsna variable in AVZMDLDU rule

-- Description:  Retrieve the result set for DW01_DSN81210_EMP (up to 10000 rows)
-- Tree Location: 10.3.58.61/1200/SQL/Data/AVZS?Virtual Tables?DW01_DSN81210_EMP
-- Remarks: DRDA - DSN81210.EMP
SELECT * FROM MDLDU_DW01_DSN81210_EMP_WHATEVER_DATASET;

1. Update rule options as needed. Figure 50 describes the VTB rule options that support Db2 unload dataset access.
   
   
   Figure 50 -  Rule options for AVZMDLDU rule