CA/Platinum Fast Unload tips

Today I'm going to discuss about a vendor product - CA(previously Platinum)'s DB2 "Fast Unload". It is a high performance DB2 data unload utility. If you use Fast Unload at your shop, go ahead and read these tips:

Fast Unload is much faster than DB2's DSNTIAUL. One of the reasons is, it does not use DB2 SQL to fetch the data. Instead it fetches the data directly from the underlying VSAM dataset. But this does not happen always. Sometimes it may have to fetch the data from the table. This is controlled by the keyword "SQL-ACCESS".

SQL-ACCESS NONE:
When you specify "NONE" for SQL-ACCESS, it does not use SQL access. If the select statement
cannot be processed using VSAM or EXCP, an error message is issued & processing ends. This is the DEFAULT.

Note: You must specify NONE to unload data from Image Copies, Concurrent Copies & DSN1COPYs.

SQL-ACCESS ONLY:
When you specify this option, it uses only SQL access to unload the tablespaces. This option lets you use any valid SELECT statement. Only one SELECT statement is processed at a time. The statements using VSAM or EXCP are processed first, followed by the SQL ACCESS SELECT statements, which are processed in the order specified in the control statements. You cannot include an ORDER-CLUSTERED clause, or the PART or OBID parameters with this option. You must specify SQL-ACCESS ONLY to use TRIM with ASCII objects. With SQL-ACCESS ONLY, DB2 determines locking. To preserve data integrity, you must specify LOCKSIZE TABLE on the tablespace.

SQL-ACCESS EXTENSION:
When you specify "EXTENSION" for SQL-ACCESS, it uses EXCP or VSAM processing if possible; otherwise uses SQL access. If you specify SQL-ACCESS EXTENSION, only those SELECT statements that require SQL access use it; all other SELECT statements are multitasked & unloaded using VSAM or EXCP. If you specify SQL-ACCESS EXTENSION with the PART or OBID parameter & SQL-ACCESS is required, an error message is issued and processing terminates.

How to find DBID, PSID, OBID of a tablespace

If you dropped a table and you took imagecopy BEFORE dropping it, then you can recover the data to a newly recreated table if you know the DBID, PSID, OBID of the dropped table. To find these values, use DSN1COPY with the image copy dataset and use FULLCOPY parm and specify 0001 for the source DBID, PSID & OBID. When you run it first, it will give the correct DBID. Replace 0001 with this DBID, you'll get the PSID. Replace 0001 with this PSID and you'll get the OBID.

For an existing table, to obtain the DBID and PSID, run the following query:

SELECT DBNAME, DBID, NAME, PSID
FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME = "database-name"
AND NAME = "table_space-name"

To obtain the table OBID, run this query:

SELECT NAME, OBID
FROM SYSIBM.SYSTABLES
WHERE TSNAME = "table_space-name"
AND CREATOR = "creator-name"

ISPF/TSO tips

• PANELID ON will show the panel name in the top left corner
• PFSHOW ON will show the PF Keys assignment at the bottom of the screen
• You are editing a PDS (partitioned dataset) member; you've made significant number of changes to the member; now you are thinking about creating a new member with these changes rather than saving the changes in the member you are editing. Can you do this? Yes, you can. Just issue this command: CRE .ZF .ZL memname. And, don't forget to issue CANCEL command on the member that you are editing, otherwise, you might save the changes accidentally (if AUTOSAVE is turned ON in your ISPF profile)
• Talking of ISPF profile, how will you know what are all the options available and which are turned on/off? Issue PROF on a dataset while in VIEW or EDIT mode.
• C ALL 'abc' 'xyz' will replace all occurrences of string abc to xyz
• C ALL 'abc' 'xyz' n will replace all occurrences of string abc to xyz in column n
• C ALL 'abc' 'xyz' n1 n2 will replace all occurrences of string abc to xyz between columns n1 and n2
• C ALL P'¬' 'abc' n1 n2 will replace ANY character found between columns n1 and n2 to abc. Use this very carefully.
• X ALL 'abc' will exclude all the lines that have the string 'abc'
• COMP X 'dataset(optional_member_name)' will display the differences between current PDS/PS and the compared dataset excluding all the lines that match. This is very useful if you are making changes to a source code and want to compare it with the baseline. There is another powerful feature available along with comparing two datasets. You can bring in the changes found in the compared dataset which is not available in the current dataset. If you would like know how to do this, please send me an email or post a comment

"HIDE EXCLUDE"

When you are editing a dataset in ISPF, you can exclude lines by typing 'x' in the line number area. When you do that, you see the excluded lines as a row of dots. You now have the option of entering 'HIDE EXCLUDE' on the command line. The line of dots disappears and is replaced with a solid line in the number area.

RESET will behave as before; RESET HIDE will return the row of dots to your display.

How to query tables from different DBMSs?

There are times when you need to query tables from different Database Management Systems (DBMSs). For example, one type of information or data may reside in DB2 UDB table and another in DB2 for z/OS or even Oracle table. How will you write queries that joins these tables and fetches the desired results?

The answer is 'federation'. If you have DB2 UDB, and want to join a table from Oracle or DB2 for z/OS, create a federated table image of the table that resides in the other DBMS. Once you create a federated table, you can write queries treating it like a table that resides on the local DB2 UDB server.

For example:
SELECT L.col1, L.col2, F.cola, F.colb
FROM Local_Table L, Federated_Table F
WHERE L.col3 = F.colc

Remember that for "federation" to work, you need DB2 Connect or a similar product.

You can find more details here:
IBM Federated Database Technology

Migrate dataset to tape

HMIGRATE command with no option specified on a dataset migrates the dataset to MIGRAT. To migrate a dataset to TAPE (MIGRAT2) directly, issue the following command:

HMIG 'dataset name' ML2

HRECALL with DFDSS option

When you need a dataset that has been migrated to tape, you can issue the HRECALL command. However, if the recalled dataset doesn’t fit in one volume the command fails. To get through this, you can have it span multi-volumes using the following command:

HSEND RECALL ‘migrated-dataset-name’ DFDSSOPTION(VOLCOUNT(ANY))

‘TTR’ in PDS and PDSE

Partitioned Data Set uses an addressing scheme called TTR (Track-Track-Record) which is based on the DASD geometry. TTR addresses are stored in hexadecimal format. So an address of X’002E26′ would mean track number X’002E’ and record X’26′. The name TTR comes from the fact that first two bytes of the address denote track number and third byte denotes record number. This dependence on the DASD geometry makes it very difficult to migrate PDS from one type of DASD to another one, e.g. from 3380 to 3390.

The PDSE addressing scheme is not dependent on the physical device geometry. It uses a ’simulated’ 3-byte TTR address to locate the members & the records which makes the migration easier. Incidentally, this simulation of addresses places some limitations on the number of members and the number of records per member in a PDSE. A TTR address of X’000001′ in a PDSE points to the directory. The addresses from X’000002′ to X’07FFFF’ point to the first record of each member, which is why there is a limit of 524,286 members. The addresses from X’100001′ to X’FFFFFF’ point to records within each member, which is why there is a limit of 15,728,639 records in each member.