Accessing IDF from DB2I (SPUFI)
Using the SQL processor using file input (SPUFI) facility to connect to DB2, you can issue SQL queries that uses 3-part names to access IBM Data Virtualization Manager for z/OS via Integrated DRDA Facility (IDF).
Example
The following selects 5 rows from a VSAM dataset mapped as STAFFVS by the IDF server:
SELECT * FROM LQ52ABCD.VDFDEMO.STAFFVS FETCH FIRST 5 ROWS ONLY;
---------+---------+---------+---------+---------+---------+---------+-
ID NAME_L NAME_D DEPT JOB YRS
---------+---------+---------+---------+---------+---------+---------+-
10 7 SANDERS 20 MGR 7
20 6 PERNAL 20 SALES 8
30 8 MARENGHI 38 MGR 5
40 7 O'BRIEN 38 SALES 6
50 5 HANES 15 MGR 10
DSNE610I NUMBER OF ROWS DISPLAYED IS 5
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+-
---------+---------+---------+---------+---------+---------+---------+-
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+-
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
DSNE621I NUMBER OF INPUT RECORDS READ IS 32
You can readily create a table alias so that the details of the three-part table name are hidden. In the following example, the alias VSTAFF is created and used instead of the fully-qualified three-part table name:
CREATE ALIAS VSTAFF FOR TABLE LQ52ABCD.VDFDEMO.STAFFVS;
---------+---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+-
COMMIT;
---------+---------+---------+---------+---------+---------+---------+-
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+-
SELECT * FROM VSTAFF FETCH FIRST 5 ROWS ONLY;
---------+---------+---------+---------+---------+---------+---------+-
ID NAME_L NAME_D DEPT JOB YRS
---------+---------+---------+---------+---------+---------+---------+-
10 7 SANDERS 20 MGR 7
20 6 PERNAL 20 SALES 8
30 8 MARENGHI 38 MGR 5
40 7 O'BRIEN 38 SALES 6
50 5 HANES 15 MGR 10
DSNE610I NUMBER OF ROWS DISPLAYED IS 5
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+-
---------+---------+---------+---------+---------+---------+---------+-
DSNE617I COMMIT PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+-
DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 3
DSNE621I NUMBER OF INPUT RECORDS READ IS 35
DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 61
You do not need to bind-copy the DB2I package into IDF before using SPUFI. IDF allows statements to be dynamically prepared in absence of a stored or bound package definition. All SQL statements entered using SPUFI are dynamically prepared.
To enable SPUFI access through IDF, bind-copy the Db2 SPUFI package DSNESPCS.DSNESM68 into IDF by using the SPUFI BIND Package function.