List of SYSIBM tables

IPLIST (V8) 
Associates multiple IP addresses to a given LOCATION

IPNAMES 
Contains the LUs associated with other systems accessible to the local DB2 subsystem

LOCATIONS 
Contains distributed location information for every accessible remote server

LULIST 
Contains the list of LUNAMEs for a given distributed location (when multiple LUNAMEs are associated with a single location)

LUMODES 
Information on distributed conversation limits

LUNAMES 
Contains information for every SNA client or server that communicates with the DB2 subsystem

MODESELECT 
Information assigning mode names to conversations supporting outgoing SQL requests

SYSAUXRELS 
Information on the auxiliary tables required for LOB columns

SYSCHECKDEP 
Column references for CHECK constraints

SYSCHECKS 
CHECK constraint specifications

SYSCHECKS2 (V7) 
Information about CHECK constraints on DB2 Catalog tables created for V7 or later

SYSCOLAUTH 
The UPDATE privileges held by DB2 users on table or view columns

SYSCOLDIST 
Cardinality, frequent value, and non-padded frequent value distribution statistics for the first key column of an index key

SYSCOLDIST_HIST (V7) 
Column distribution statistics history

SYSCOLDISTSTATS 
The non-uniform distribution statistics for the ten most frequently occurring values for the first key column in a partitioned index

SYSCOLSTATS 
The partition statistics for selected columns

SYSCOLUMNS 
Information about every column of every DB2 table and view

SYSCOLUMNS_HIST (V7) 
Historical column statistics

SYSCONSTDEP 
Information regarding columns that are dependent on CHECK constraints and user-defined defaults

SYSCOPY 
Information on the execution of DB2 utilities required by DB2 recovery

SYSDATABASE 
Information about every DB2 database

SYSDATATYPES 
Information about the user-defined distinct types defined to the DB2 subsystem

SYSDBAUTH 
Database privileges held by DB2 users

SYSDBRM 
DBRM information only for DBRMs bound into DB2 plans

SYSDUMMY1 
Contains no information; this table is for use in SQL statements requiring a table reference without regard to data content

SYSFIELDS 
Information on field procedures implemented for DB2 tables

SYSFOREIGNKEYS 
Information about all columns participating in foreign keys

SYSINDEXES 
Information about every DB2 index

SYSINDEXES_HIST (V7) 
Historical index statistics

SYSINDEXPART 
Information about the physical structure and storage of every DB2 index

SYSINDEXPART_HIST (V7) 
Historical index partition statistics

SYSINDEXSTATS 
Partitioned index statistics by partition

SYSINDEXSTATS_HIST (V7) 
Historical partitioned index statistics by partition

SYSJARCONTENTS (V7) 
Java class source for installed JAR

SYSJARDATA (V7) 
Auxiliary table for the BLOB data from SYSJAROBJECTS

SYSJAROBJECTS (V7) 
The contents of the installed JAR

SYSJARCLASS_SOURCE (V7) 
Auxiliary table for the CLOB data from SYSJARCONTENTS

SYSKEYCOLUSE (V7) 
Columns that participate in unique constraints (primary key or unique key) from the SYSTABCONST table

SYSKEYS 
Information about every column of every DB2 index

SYSLINKS 
Information about the links between DB2 Catalog tables

SYSLOBSTATS 
Statistical information for LOB table spaces

SYSLOBSTATS_HIST (V7) 
Historical LOB statistics

SYSPACKAGE 
Information about every package known to DB2

SYSPACKAUTH 
Package privileges held by DB2 users

SYSPACKDEP 
A cross-reference of DB2 objects required for DB2 packages

SYSPACKLIST 
The package list for plans bound specifying packages

SYSPACKSTMT 
All SQL statements contained in each DB2 package

SYSPARMS 
Parameters for defined routines

SYSPKSYSTEM 
The systems (such as CICS, IMS, or batch) enabled for DB2 packages

SYSPLAN 
Information about every plan known to DB2SYSPLANSYSPLANAUTHPlan privileges held by DB2 users

SYSPLANDEP 
A cross-reference of DB2 objects required by DB2 plans

SYSPLSYSTEM 
The systems (such as CICS, IMS, or batch) enabled for DB2 plans

SYSPROCEDURES 
The stored procedures available to the DB2 subsystem

SYSRELS 
The referential integrity information for every relationship defined to DB2

SYSRESAUTH 
Resource privileges held by DB2 users

SYSROUTINEAUTH 
Privileges held by DB2 users on routines

SYSROUTINES 
Information about every routine (that is, user-defined functions and stored procedures) defined to the DB2 subsystem

SYSROUTINES_OPTS (V7) 
Information about the options used by DB2-generated routines

SYSROUTINES_SRC (V7) 
The source code for routines generated by DB2

SYSSCHEMAAUTH 
Schema privileges granted to users

SYSSEQUENCEAUTH (V8) 
Privileges held by DB2 users on SEQUENCE objects

SYSSEQUENCES (V7) 
Information about identity columns and SEQUENCE objects

SYSSEQUENCESDEP (V8) 
Records the dependencies of identity columns on tables

SYSSTMT 
All SQL statements contained in each DB2 plan bound from a DBRM

SYSSTOGROUP 
Information about every DB2 storage group

SYSSTRINGS 
Character conversion information

SYSSYNONYMS 
Information about every DB2 synonym

SYSTABAUTH 
Table privileges held by DB2 users

SYSTABCONST (V7) 
Information about every unique constraint (primary key or unique key) created in DB2 V7 or later

SYSTABLEPART 
Information about the physical structure and storage of every DB2 table space

SYSTABLEPART_HIST (V7) 
Historical table space partition statistics

SYSTABLES 
Information about every DB2 table

SYSTABLES_HIST (V7) 
Table statistics history

SYSTABLESPACE 
Information about every DB2 table space

SYSTABSTATS 
Partitioned table space statistics by partition

SYSTABSTATS_HIST (V7) 
Historical partitioned table space statistics by partition

SYSTRIGGERS 
Information about every trigger defined to the DB2 subsystem

SYSUSERAUTH 
System privileges held by DB2 users

SYSVIEWDEP 
A cross-reference of DB2 objects required by DB2 views

SYSVIEWS 
The SQL CREATE VIEW statement for every DB2 view

SYSVLTREE 
A portion of the internal representation of complex or long views

SYSVOLUMES 
A cross-reference of DASD volumes assigned to DB2 storage groups

SYSVTREE 
The first 4000 bytes of the internal representation of the view; the remaining portion of longer or complex views is stored in SYSVLTREE

USERNAMES 
Outbound and inbound ID translation information

Search members by middle string in a given PDS

Use ISPF 4.12 option to search for members in a PDS by the middle string of the members.
Consider this situation .

I have a PDS and I want to search it for member only by middle string of the members.
e.g. there are some members like BESTJCL1,RESTJCL2,PESTCBL1, BESTCBL2,
WESTCBL3,TESTASM1,BESTASM2...etc
I want to find members which have CBL in them.

Use ISPF 4.12 option.
Specify the dataset to be searched and the pattern of the string.
Specify the LANG type as COB
And select the browse option.

To search a member in a set of PDS list
In 3.4 , first list all datasets that you would liike to search
Use Command "M "in the command line

Member in Use

While trying to open any member in a PDS, we sometimes come across
"member in use" message. In that situation, if you want to know who is using the member
currently, press F1 twice.

SAREA

To know about TSO Region that you are currently working in, Issue SAREA in command line

ISPF STATISTICS Pop-up window will show you the region( and other info too)

ISPF TIP #002

Usually the PDS or PS bears the ID of the creator or the ID of the person who modified it
recently. You can change these ID values without leaving trace of one’s own ID

In the command area against the member name or PS name, by giving ‘G’.
Dialog box will pop up allowing you to change the ID values.

Very important about locks !!!!

IN (Intent None) Table spaces, tables The lock owner can read any data in the table, including uncommitted data, but cannot update any of it. No row locks are acquired by the lock owner. Other concurrent applications can read or update the table.

IS (Intent Share) Table spaces, tables The lock owner can read data in the locked table, but not update this data. When an application holds the IS table lock, the application acquires an S or NS lock on each row read. In either case, other applications can read or update the table.

NS (Next Key Share) Rows The lock owner and all concurrent applications can read, but not update, the locked row. This lock is acquired on rows of a table, instead of an S lock, where the isolation level is either RS or CS on data that is read.

S (Share) Rows, tables The lock owner and all concurrent applications can read, but not update, the locked data. Individual rows of a table can be S locked. If a table is S locked, no row locks are necessary.

IX (Intent Exclusive) Table spaces, tables The lock owner and concurrent applications can read and update data in the table. When the lock owner reads data, an S, NS, X, or U lock is acquired on each row read. An X lock is also acquired on each row that the lock owner updates. Other concurrent applications can both read and update the table.

SIX (Share with Intent Exclusive) Tables The lock owner can read and update data in the table. The lock owner acquires X locks on the rows it updates, but acquires no locks on rows that it reads. Other concurrent applications can read the table.

U (Update) Rows, tables The lock owner can update data in the locked row or table. The lock owner acquires X locks on the rows before it updates the rows. Other units of work can read the data in the locked row or table; but cannot attempt to update it.

NX (Next Key Exclusive) Rows The lock owner can read but not update the locked row. This mode is similar to an X lock except that it is compatible with the NS lock.

NW (Next Key Weak Exclusive) Rows This lock is acquired on the next row when a row is inserted into the index of a non-catalog table. The lock owner can read but not update the locked row. This mode is similar to X and NX locks except that it is compatible with the W and NS locks.

X (Exclusive) Rows, tables The lock owner can both read and update data in the locked row or table. Tables can be Exclusive locked, meaning that no row locks are acquired on rows in those tables. Only uncommitted read applications can access the locked table.

W (Weak Exclusive) Rows This lock is acquired on the row when a row is inserted into a non-catalog table. The lock owner can change the locked row. This lock is similar to an X lock except that it is compatible with the NW lock. Only uncommitted read applications can access the locked row.

Z (Super exclusive) Table spaces, tables This lock is acquired on a table in certain conditions, such as when the table is altered or dropped, an index on the table is created or dropped, or a table is reorganized. No other concurrent application can read or update the table.

Note: Only tables and table spaces will obtain the "intent" lock modes. Thatis, intent locks are not obtained for rows.

ISPF TIP #001

COMPARE SESSION:

COMPARE SESSION on the command line will give the changes that you have done since the last SAVE command when you are editing a file/program.

Further use of COMPARE commands...
- Can be used like 3.13 option

DASD Best Practices

See below for Mainframe DASD Best Practices developed by Larry Strauss in the Capacity Planning and Management team. By following these DASD practices we can help reduce our IPF DASD cost expense which has doubled since implementing XRC.

DASD Best Practices:
• Use system determined block size when writing sequential datasets
o Code BLKSIZE=0 in the JCL, the system will determine the optimal block size for the device being written to.
o COBOL programs should be coded BLOCK CONTAINS 0 RECORDS
o Optimal blocking will reduce the DASD space needed, save on I/O, run time, and CPU required to process data
o Free unused DASD when dataset is closed
o Code RLSE parameter on dataset space allocation to recover unused space
o Do not code RLSE on PDS datasets when member maintenance will require the allocated space
o VSAM datasets should be allocated using DATACLAS=EXT4GB
o Allows for extended format
o Allows for extended addressability
o LARGE DATASETS - To minimize allocation failure when allocating large datasets, and reduce the effect of DASD space issues. Allocate DASD extents no greater than 350 Cylinders. There is a higher likelihood of finding a smaller primary quantity on a volume than finding a volume that has say 1000 cylinders of free space.
o For example a job needs 1000 cylinders,
 DO NOT allocate SPACE=(CYL,(1000,100),RLSE)
 DO allocate SPACE=(CYL,(350,330),RLSE)
 SMALLER DATASETS - In general when requesting space for DASD datasets, allocate DASD extents no greater than 350 Cylinders. Allocate the space expected for the dataset as the primary quantity, and allocate the secondary quantity as about 10% of the primary.
o For example a job needs 300 cylinders,
 DO NOT allocate SPACE=(CYL,(30,30),RLSE)
 DO NOT allocate SPACE=(CYL,(300,300),RLSE)
 DO allocate SPACE=(CYL,(300,30),RLSE)
 Code optional BUFNO parameters for sequential and BUFND BUFNI parameters for VSAM
o Default buffers (5 for sequential datasets), (2 data and 1 index for VSAM datasets) are often inadequate. Use additional buffers especially when large numbers of records will be processed
o Additional buffers can reduce physical I/O, reduce run time and save CPU required to process the data
o Use SMB (system managed buffering) or BLSR (batch local shared resources) when appropriate on VSAM batch with high I/O

If you have any questions on these best practices you can contact Larry Strauss at 224-405-3059.

Thanks,
Ken Kieliszewski - AD Core Operations
Discover Financial Services
Work: 224-405-2510

To Alter the GDG limit

Here is the command and a JCL to change the GDG limit. It doesn’t matters what was the earlier limit, it will change to the limit specified now.

1. TSO
From any command line:
TSO ALTER 'YOUR.FILE.NAME' LIMIT (30)
or from Option 6:
ALTER 'YOUR.FILE.NAME LIMIT' (30)

2. BATCH
Run an IDCAMS job:
(Supply your own JOB card)
//IDCAMS EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
ALTER YOUR.FILE.NAME LIMIT (30)
/*
//
(Be sure there is a space before the ALTER in an IDCAMS job)

Also to know more about the ALTER command, please refer the below site. It has many functions apart from limit change for GDG.

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DGT2I240/5.0?SHELF=DGT2BK50&DT=20050629061743

Cursor Optimization Tips

Try to avoid using SQL Server cursors, whenever possible.

SQL Server cursors can results in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

Do not forget to close SQL Server cursor when its result set is not needed.

To close SQL Server cursor, you can use CLOSE {cursor_name} command. This command releases the cursor result set and frees any cursor locks held on the rows on which the cursor is positioned.

Do not forget to deallocate SQL Server cursor when the data structures comprising the cursor are not needed.

To deallocate SQL Server cursor, you can use DEALLOCATE {cursor_name} command. This command removes a cursor reference and releases the data structures comprising the cursor.

Try to reduce the number of records to process in the cursor.

To reduce the cursor result set, use the WHERE clause in the cursor's select statement. It can increase cursor performance and reduce SQL Server overhead.

Try to reduce the number of columns to process in the cursor.

Include in the cursor's select statement only necessary columns. It will reduce the cursor result set. So, the cursor will use fewer resources. It can increase cursor performance and reduce SQL Server overhead.

Use READ ONLY cursors, whenever possible, instead of updatable cursors.

Because using cursors can reduce concurrency and lead to unnecessary locking, try to use READ ONLY cursors, if you do not need to update cursor result set.

Try avoid using insensitive, static and keyset cursors, whenever possible.

These types of cursor produce the largest amount of overhead on SQL Server, because they cause a temporary table to be created in TEMPDB, which results in some performance degradation.

Use FAST_FORWARD cursors, whenever possible.

The FAST_FORWARD cursors produce the least amount of overhead on SQL Server, because there are read-only cursors and can only be scrolled from the first to the last row. Use FAST_FORWARD cursor if you do not need to update cursor result set and the FETCH NEXT will be the only used fetch option.

Use FORWARD_ONLY cursors, if you need updatable cursor and the FETCH NEXT will be the only used fetch option.

If you need read-only cursor and the FETCH NEXT will be the only used fetch option, try to use FAST_FORWARD cursor instead of FORWARD_ONLY cursor. By the way, if one of the FAST_FORWARD or FORWARD_ONLY is specified the other cannot be specified.