Ranking and Numbering for Records in DB2

DB2 supports ranking and numbering in much the same way that Oracle does.

The available functions are:

• ROW_NUMBER(), which simply numbers the returned rows sequentially
• RANK(), which ranks the results, but, in the case of a tie, gives the same number to each and leaves a gap to compensate
• DENSE_RANK() operates the same as RANK() but doesn't leave any gaps.

The difference in these three methods can be seen in Listing 4.

Listing 1. Ranking and Numbering Results

   select 
     client, MONTHEND, SALEVOL, 
     ROW_NUMBER() over (order by SALEVOL desc) AS RN, 
     RANK() over (order by SALEVOL desc) AS RANK, 
     DENSE_RANK() over (order by SALEVOL desc) AS DENSE
   from mysales 
   where MONTHEND=DATE('1997-11-30')
   order by RN
   
   CLIENT     MONTHEND   SALEVOL   RN  RANK DENSE
   CYRIX      11/30/1997    120     1     1     1
   BIG BLUE   11/30/1997    106     2     2     2
   EGGHEAD    11/30/1997    106     3     2     2
   DEVX       11/30/1997     80     4     4     3
   FIGTREE    11/30/1997     62     5     5     4
   ACME       11/30/1997     20     6     6     5

The results need not be returned in rank order. We may wish to show each client's rank while listing them alphabetically

Listing 2. Return Ranks in Any Order

   select
     CLIENT, SALEVOL,
     RANK() over (order by SALEVOL desc) AS RANK
   from mysales 
   where MONTHEND=DATE('1997-11-30') 
   order by CLIENT
   
   CLIENT     SALEVOL RANK
   ACME            20    6
   BIG BLUE       106    2
   CYRIX          120    1
   DEVX            80    4
   EGGHEAD        106    2
   FIGTREE         62    5

One application of ROW_NUMBER is to select a numbered range of rows from the middle of your results. For example, you could retrieve the next three clients following the top two as shown in Listing 6.

Listing 3. Selecting Rows by Number

   WITH ALLSALES AS 
      (SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL,
      ROW_NUMBER() OVER
         (ORDER BY SUM(SALEVOL) DESC, CLIENT)
      AS RN
      FROM MYSALES 
      GROUP BY CLIENT) 
   SELECT 
      CLIENT, TOTALVOL, RN
   FROM ALLSALES
   WHERE RN BETWEEN 3 AND 5
   ORDER BY RN
   
   CLIENT     TOTALVOL  RN
   BIG BLUE       4781   3
   FIGTREE        3986   4
   ACME           3044   5

Selecting first few rows in DB2

DB2's method of performing a Top-N query is the FETCH FIRST clause. You can append these variations to a regular SELECT query:

• FETCH FIRST ROW ONLY
• FETCH FIRST 1 ROW ONLY
• FETCH FIRST integer ROWS ONLY

Interestingly, you can also use

• FETCH FIRST 1 ROWS ONLY
• FETCH FIRST 5 ROW ONLY

They aren't as nice grammatically, but they make it easier to generate queries automatically—you don't have to worry about whether to say ROW or ROWS.


Now, we can ask for a single record as follows:

Listing 1. Return a Single Row

   SELECT * FROM MYSALES FETCH FIRST ROW ONLY
   
   CLIENT     MONTHEND   SALEVOL 
   DEVX       03/31/1998     100

We have retrieved one row, but there's no way to know ahead of time which row it will be.(HINT: This does give us a handy way to remind ourselves what fields are in a table, with a row of sample data as a bonus!)

To find our top two clients, use the code in Listing 3.

Listing 2: Show Top Two Clients

   SELECT CLIENT, SUM(SALEVOL) AS TOTALVOL
   FROM MYSALES 
   GROUP BY CLIENT 
   ORDER BY SUM(SALEVOL) DESC 
   FETCH FIRST 2 ROWS ONLY
   
   CLIENT     TOTALVOL   
   DEVX           5785
   EGGHEAD        5341

INSPECT in COBOL

The INSPECT verb has two options, TALLYING and REPLACING. You can do one or the other or both. If both are done, the TALLYING IS DONE BEFORE THE replacing. Some versions of the INSPECT require that all the literals be in quotes. This may call for a redefination if the field is numeric.
In using the TALLYING format of the INSPECT, you are tallying into a field that is a counter. This field must be initialized to 0 before the inspect is done. Unsually this is done by a MOVE 0 to whatever the counter is called.

The TALLYING option of the INSPECT has multiple options - in fact, some versions have options beyond those required by the COBOL specifications:

• You can tally ALL of something
• You can tally just the LEADING occurances of something
• You can just tally CHARACTERS
• You can tally BEFORE or AFTER an INITIAL specified character

Example #1: This statement counts the number of occurrences of the digit 5 until the first space is encountered. If FLA = 256545 67 then after the INSPECT, CTRA would equal 3.
MOVE 0 TO CTRA.
INSPECT FLDA TALLY CTRA
    FOR ALL "5" BEFORE INTIAL SPACE.

Example #2: This statement counts the number of spaces in the field FLDB.
If FLB = 4 5 6 1 2 then after the INSPECT, CTRB would equal 4 since there is a space between each number.
MOVE 0 TO CTRB.
INSPECT FLDB TALLYING CTRB
    FOR ALL SPACES.

Example #3: In this example, the count of all characters before the first space is tallied. If FLDC is equal to 16AB5 6 then CTRC will contain the number 5 since 1, 6, A, B, and 5 proceed the space before the 6.
MOVE 0 TO CTRC.
INSPECT FLDC TALLYING CTRC
    FOR CHARACTERS BEFORE INITIAL SPACE.

Example #4: This statement counts all of the leading zeros in a field. Embedded or trailing zeros are counted. IF FLDD is equal to 00090020, then CTRD is equal to 3.
MOVE 0 TO CTRD.
INSPECT FLDD TALLYING CTRD
    FOR LEADING ZEROS.

Example #5: This statement counts all zeros in the field. IF FLDE is equal to 00090020, then CTRE will be equal to 6.
MOVE 0 TO CTRE.
INSPECT FLDE TALLYING CTRE
    FOR ALL ZEROS.
Example #6: This statement tallies for all zeroes that proceed the initial 2 In this case, CTRF will be 5. FLDF is equal to 00090020.
INSPECT FLDF TALLYING CTRF
    FOR ALL ZEROS BEFORE INITIAL 2.

Example #7: This statement counts the spaces before the number 5. In this example CTRG will be 3. FLDG has three spaces and then the number 1257 ( 1257).
INSPECT FLDG TALLYING CTRG
    FOR LEADING SPACE BEFORE INITIAL 5.

Example #8: This statement will replace all B with G. FLDH will start as ABCBDFB and will become AGCGDFG.
INSPECT FLDH REPLACING ALL "B" BY "G".

Example #9: This replace will change all A to X. This means that AAABBAAA will become XXXBBAAA.

INSPECT FLDI REPLACING CHARACTERS BY "X"
    BEFORE INITIAL "B".

Example #10: This will replace the first X with a 5. There ACXDGXB will become AC5DGXB.

INSPECT FLDJ REPLACING FIRST "X" BY "5".

COBOL Numeric Data Types - COMP

The following common COBOL data types are discussed below:

• Binary
• Computational (comp)
• Comp-1
• Comp-2
• Comp-3
• Packed Decimal

BINARY
Specified for binary data items. Such items have a decimal equivalent consisting of the decimal digits 0 through 9, plus a sign. Negative numbers are represented as the two's complement of the positive number with the same absolute value.  The amount of storage occupied by a binary item depends on the number  of decimal digits defined in its PICTURE clause:

Digits in PICTURE Clause Storage Occupied
1 through 4 2 bytes (halfword)
5 through 9 4 bytes (fullword)
10 through 18 8 bytes (doubleword) │
The leftmost bit of the storage area is the operational sign.

PACKED-DECIMAL
Specified for internal decimal items. Such an item appears in storage  in packed decimal format. There are 2 digits for each character  position, except for the trailing character position, which is  occupied by the low-order digit and the sign. Such an item can  contain any of the digits 0 through 9, plus a sign, representing a  value not exceeding 18 decimal digits.

The sign representation uses the same bit configuration as the 4-bit  sign representation in zoned decimal fields.

COMPUTATIONAL or COMP
Representation of the COMPUTATIONAL phrase is system-dependent and is  normally assigned to representations that yield the greatest  efficiency when arithmetic operations are performed on that system.

COMPUTATIONAL-1 or COMP-1
Specified for internal floating-point items (single precision).  COMP-1 items are 4 bytes long. The sign is contained in the first bit  of the leftmost byte and the exponent is contained in the remaining 7  bits. The last 3 bytes contain the mantissa.

COMPUTATIONAL-2 or COMP-2
Specified for internal floating-point items (double precision). COMP-2 items are 8 bytes long. The sign is contained in the first bit  of the leftmost byte and the remaining 7 bits contain the exponent.  The remaining 7 bytes contain the mantissa.

COMPUTATIONAL-3 or COMP-3 (internal decimal)
For VS COBOL II, this is the equivalent of PACKED-DECIMAL.

COMPUTATIONAL-4 or COMP-4 (binary)
For VS COBOL II this is the equivalent of BINARY.

Special Searching

A picture string in a FIND, CHANGE, or EXCLUDE command allows you to search for a particular kind of character without regard for the specific character involved. 

You can use special characters within the picture string to represent the kind of character to be found, as follows:

They can be used in REXX / ISREDIT macros too.  

String Meaning

P'='   Any character
P'¬'   Any character that is not a blank
P'.'    Any character that cannot be displayed
P'#'   Any numeric character, 0-9
P'-'    Any non-numeric character
P'@'   Any alphabetic character, uppercase or lowercase
P'c'    Any lowercase alphabetic character
P'>'   Any uppercase alphabetic character
P'$'   Any special character, neither alphabetic nor numeric.

TIME=NOLIMIT and TIME=MAXIMUM

We can specify the max limit of TIME parameter as
TIME=NOLIMIT or
TIME=MAXIMUM.

Now which specification gives maximum time for a JOB?

TIME=MAXIMUM will allow the job to run for 357912 minutes (248.55 days)

TIME=NOLIMIT will allow the job for unlimited amount of time

Another advantage of NOLIMIT option is that it can remain in wait status for more than the installation
time limit.

Run a step only from a set of steps in a job

I have a JCL with 20 steps. Due to some reasons I want to execute the step 15 only.

One way to do it is to use RESTART from STEP15, but it will try to execute the subsequent steps too. We have to insert null statement after step15 to prevent the execution of subsequent steps.

But one decent way is there in which we don’t need to touch the job steps code, but alter only the Jobcard.

And that is....

In the JOBCARD, code COND parameter. Then, when the Job is executed , only the Step mentioned in the RESTART parameter will get executed.

RESTART=STEP15,COND=(0,LE)

DB2 Index with Expressions

Prior to DB2 9, the create index statement only allowed you to use the column name from the table the index is being built on. The same value for each column stored on the table was copied into the index and used to quickly identify the row when searching by that column name. Now with DB2 9, the create index statement supports a feature known as key expressions. Here's why key expressions should interest you.

Many times when you're writing complex queries, you'd like to the optimizer to use an index, but instead it performs a table space scan. These situations usually occur when you're using scalar functions or arithmetic calculations. Let's consider a couple of examples:

Say you have a column on the table called lastname and the data in this column is stored in lowercase. But because end users can enter last names in upper and lower case, you must convert everything to upper case to perform the search. The following sample contains DDL to create an index on the table EMPL and then SQL to search for employees with a last name of ‘COLEMAN’.

CREATE INDEX EMPL_X1 ON EMPL
  (lastname);

SELECT EMPNO, FIRSTNME, LASTNAME, SALARY, BONUS, COMM
FROM EMPL
WHERE UPPER(lastname) = ‘COLEMAN’

No match: In this sample, DB2 cannot use index EMPL_X1 because of the scalar function UPPER. To resolve this in DB2 9, you can use an expression to store the data in the index as upper case.

CREATE INDEX EMPL_X2 ON EMPL
  UPPER(lastname);

Now DB2 can use index EMPL_X2 and match on the data.

Next is is an example of the types of queries you'd see in a data decision support or data warehousing application. Say you need to present a list of employees that have a total compensation package greater than $12,000 a month. The compensation package is made up of salary, bonus and commission. In DB2 8 your index on the columns may be defined this way:

CREATE INDEX EMPL_X3 ON EMPL
  (salary, bonus, comm);

SELECT EMPNO, FIRSTNME, LASTNAME, SALARY, BONUS, COMM
FROM EMPL
WHERE salary + bonus + comm. > 12000.00

However, due to the arithmetic calculations in the where clause, the optimizer cannot use the EMPL_X3 index to retrieve all employees making more than $12,000. With DB2 9 though you can create an index with the expression to calculate the total and store this in the index. The optimizer can now match on the column because the data was precalculated and stored in the index. Index EMPL_X4 shows how you'd use a key expression to calculate and store the results in an index.

CREATE INDEX EMPL_X4 ON EMPL
  (salary + bonus + comm);

If you're going to use expressions, consult the DB2 9 SQL Reference guide. A list of rules and restrictions can be found under the CREATE INDEX statement.

The basic rule is that you must reference at least one column from the table. The column named must not be of type LOB, XML or DECFLOAT. The referenced columns cannot include any FIELDPROCs or a SECURITY LABEL.

Also, key expressions must not include:

• A subquery -- an aggregate function
• A not deterministic function
• A function that has an external action
• A user-defined function
• A sequence reference
• A host variable
• A parameter marker
• A special register
• A CASE expression
• An OLAP specification

Run a Program using Referback

It is possible to execute a program from any library using the referback feature.

//STEP1 EXEC PGM=IEFBR14
//PROGRAM DD DSN=SYSTEM.PGM.LOADLIB(COBOLPGM),DISP=SHR

.
.
.
.
//STEP2 EXEC PGM=*.STEP1.PROGRAM

Like the DB2 LIKE

The LIKE phrase defines a mask for comparing characters:
   WHERE COL_VAL [NOT] LIKE mask

 

A mask may be a host variable or a literal enclosed in quotes and may contain any number of:

character literal		for an exact match
underscore character	_	for any single character
percent sign character	%	for any sequence of characters of length 0 or more

For example:

		'NEW %'	masks	'NEW YORK' but not 'NEWARK'
		'T_N'	masks	'TAN', 'TIN', or 'TON', but not 'TUNE'
		'T_N%'	masks	'TUNE'
		'%CA%'	masks	'CAT', 'GO CART', 'MOCA', etc.
		'%CA% '	masks	'CAT ' but not 'CAT'

To use a host variable for a mask to produce the same effect as the literal mask in the second-to-last example, code it right-padded with “%” characters to avoid the effect of the last example.

    05  WS-MASK           PIC  X(6)  VALUE '%CA%%%'.

 

The IN phrase chooses from a given set:
   WHERE COL_VAL [NOT] IN (:HOST-VAR, 'LITERAL', COL1 + COL2, ...)
 

Multiple list items that contain the same value are considered as a single item.

The BETWEEN phrase chooses from a range of inclusive limits:

   WHERE COL_VAL [NOT] BETWEEN [:HOST-VAR1, 'LIT1']

                           AND [:HOST-VAR2, 'LIT2']

Handling NULL in DB2

The value of an indicator variable tells the status of a row after a query.

01  FILLER.

    05  WS-AMOUNT         PIC S9(5)V9(2) COMP-3.  Host variables

    05  WS-CUSTNUM        PIC  X(5).

01  FILLER.

    05  AMT-IND           PIC S9(4) COMP.         Indicator variable

    EXEC SQL

      SELECT CUST_AMOUNT

        INTO :WS-AMOUNT:AMT-IND       No intervening space

        FROM T100.CUST

       WHERE CUST_ID = :WS-CUSTNUM

    END-EXEC.

After a query, the indicator variable contains the following:

0		Column is not null
-1		Column is null
-2		Column is null as result of conversion error
+length		Full length of column that was truncated to fit a short host variable

 

Load -1 to the indicator variable to set a column to a null value, during UPDATE or INSERTof a row.

 

If a column is always to be set to a null value, code the NULL keyword for the column: in the UPDATE statement’s SET clause; or in the INSERT statement’s VALUES clause.

A column omitted from the row list of an INSERT statement will always be set to a null value, if the column was defined as NOT NULL; otherwise, an error will occur.

 

Code a predicate to test for null with the following syntax:
   WHERE column name IS [NOT] NULL

 

The scalar functions, VALUE and COALESCE, are equivalent, and they can be used only in outer joins; each takes a list of multiple parameters and returns the first parameter that is not null.  

 

The following will return either a non-null column value or a literal:

    EXEC SQL

        SELECT ACCT_REG

              ,VALUE(ACCT_A1, ACCT_A2, 'NO ACCT') 

          INTO :WS-REGION

                ,:WS-ACCOUNT

          FROM REGS.TABLE_EMP

         WHERE ACCT_REG <> '65'

    END-EXEC.

Isolation level in DB2

1.SERIALIZABLE(Repeatable read (RR))

2.REPEATABLE READ(Read stability (RS))

3.READ COMMITTED(Cursor stability (CS)) (default)

4.READ UNCOMMITTED(Uncommitted read (UR))

SERIALIZABLE (DB2 UDB: Repeatable Read)

    Locks the table within a unit of work. An application can retrieve and operate on rows in the table as many times as needed. However, the entire table is locked, not just the rows that are retrieved. Until the unit of work completes, no other application can update, delete, or insert a row that would affect the table.

    SERIALIZABLE applications cannot see uncommitted changes made by other applications. Therefore, a SELECT statement issued repeatedly within the unit of work gives the same result each time. Lost updates, access to uncommitted data, and phantom rows are not possible.

REPEATABLE READ (DB2 UDB: Read Stability)

    Because DB2 Everyplace locks entire tables (not specific rows), REPEATABLE READ behaves exactly like SERIALIZABLE.

READ COMMITTED (DB2 UDB: Cursor Stability)

    The entire table is locked. Shared locks are released when the associated cursors are closed (isolation levels higher than READ COMMITTED hold shared locks until the end of a transaction). Exclusive locks are held until the end of the transaction.

    No other application can perform any DML operation on a table while an open cursor is accessing it. READ COMMITTED applications cannot see uncommitted changes of other applications.

    Both non-repeatable reads and phantom reads are possible. READ COMMITTED is the default isolation level, allowing maximum concurrency while seeing only committed rows from other applications.

READ UNCOMMITTED (DB2 UDB: Uncommitted Read)

    An application can access some uncommitted changes of other transactions: tables and indexes that are being created or dropped by other transactions are not available while the transaction is processing. Any other changes can be read before they are committed or rolled back.

    At this level, the application does not lock other applications out of the table it is reading.

Freezing columns in File-Aid

While working in MS Excel we have the option of “freeze Panes”. By this options we can freeze some columns and have other columns scrolling. This feature is helpful in analysis when there are lots of columns(fields) in a file.

In mainframe too, we have similar such facility thru File-Aid.

1.Open the file in File-Aid
2. use VFMT format
3. If you want to freeze columns(fields) 1,2,3, and 6 and have the rest as scrollable issue the below command .

HOLD 1-3,6

VSAM record count using File-Aid

Whats the best way to count the number of records in a VSAM File?

1. Choose FILE-AID Option 3.8.
2. At the prompt of ....ENTER NEXT FUNCTION OR END , enter TALLY.
3. You will get the record count.
4. Type END to exit.

Read Only VSM Files

By using INHIBIT along with ALTER command, we can have a read-only VSAM dataset.

Example:

//STEP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
ALTER -
SECRET.KSDS.DATA -
INHIBIT
ALTER -
SECRET.KSDS.INDEX -
INHIBIT
/*

Notice that the ALTER command is used with DATA and INDEX and not with the cluster.

Dummy VSM Dataset in JCL

In general we use DUMMY datasets in JCL instead of a actual file for some requirements.

But how can we specify dummy VSAM dataset in a JCL?

The parameter AMP=’AMORG’ tells the OS to treat the file as VSAM file.

//NOVSAMIO DD DUMMY,AMP=’AMORG’

TSQ Vs. TDQ

• In Temporary Storage Queues Data is read randomly, while in Transient Data Queues data must be read sequentially.
• In a TSQ data can be read any number of times as it remains in the queue until the entire Queue is deleted. In TDQ data item can be read once only. To reuse the TDQ it must be closed and reopened.
• Data can be changed in TSQ, but not in TDQ.
• Auxiliary or Main Storage, while TDQ is written to Disk. Temporary storage is a holding place, while Transient data is always associated with destination.
• TSQ name is defined dynamically, while a TDQ name need to be defined in the DCT.

Note: An application uses TSQ 's to pass info' from task to task, while a TDQ to accumulate records before processing or send data for external use, such as a print operation or other.

Find bad data using File-Aid

The Easiest and Coolest way to locate bad data is thru File-Aid's FIND command.

1. OPEN the file in FILE-AID (in either browse or edit mode) 
2. XREF with COPYBOOK. 
3. Use FMT mode. 
4. Then issue the below command. 

F /field-name INVALID
or
F /field-number INVALID

The control will take you to the first invalid data record for the given field.

Example: The FILE has 3 fields namely NAME,AGE,COUNTRY. If you want to find the invalid data in the age field, then issue F /2 INVALID.

Points to Ponder on DB2 NULL

• NULLs can present problems because they are handled differently by different computers and the collating sequence is inconsistent with regard to NULLs.
  
  
• Unless you specify NOT NULL, the default is to allow for NULLs
  
  
• It's easy for us to get lazy and allow columns to contain NULLs when it would be better to specify NOT NULL
  
  
• Remember to allow for NULLs creating UNKNOWN logical values. Always test your code with NULLs in all possible places.
  
  
• The NULL is a global creature, not belonging to any particular data type, but able to replace any of their values.
  
  
• A NULL isn't a zero, it isn't a blank string, it isn't a string of length zero.
  
  
• The basic rule for math with NULLs is that they propagate. An arithmetic operation with a NULL will return a NULL. If you have a NULL in an expression, the result will be NULL.
  
  
• If you concatenate a zero length string to another string, that string stays the same. If you concatenate a NULL string to a string, the string becomes a NULL.
  
  
• In comparisons, the results can be TRUE, FALSE, or UNKNOWN. A NULL in a row will give an UNKNOWN result in the comparison.
  
  
• Sometimes negating the wording of the problem helps. Instead of saying "Give me the cars that met all the test criteria," say "Don't give me any car that failed one of the test criteria." It is often easier to find what you do not want than what you do want. This is very true when you use the NOT EXISTS, but beware of NULLs and empty tables when you try this.
  
  
• You can't completely avoid NULLs in SQL. However, it is a good idea to try as hard as you can to avoid them whenever possible.
  
  
• Make yourself think about whether you really need NULLs to exist in a column before you omit the NOT NULL clause on the column definition.
  
  
• Use NULLs sparingly
  
  

Explanation on DB2 Explain

When an SQL is executed against or bound to a DB2 database, DB2 Optimizer tool defines the access path used to access the data. This access path is defined according to tables’ statistics generated by DB2 Runstats tool.

The Explain command details the access path defined by DB2 and allows you to analyze how the data will be accessed and how you can improve the command’s performance.

A table called PLAN_TABLE which has your user id as its owner is required for working on DB2 Explain. You can create it by using the below SQL.

CREATE TABLE [userid].PLAN_TABLE LIKE DEFAULT.PLAN_TABLE;

Once the above mentioned table is ready, follow the below steps.

Step One:
Execute the Explain command on your general SQL statement which you want to analyze:

EXPLAIN PLAN SET QUERYNO = 1 FOR
– [your sql statement here];

This command will put the Explain information in the PLAN_TABLE.

Step Two:
Execute this SQL command to view the Explain information:

SELECT *
FROM PLAN_TABLE
WHERE
QUERYNO = 1
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ
WITH UR;

QUERYNO should be the same used in the explain command on Step 1.

Step Three:
Look at these fields for important information:

PLANNO – Number of steps necessary to process the query indicated in QBLOCKNO.
METHOD – Indicate joins method used for the step (PLANNO).
ACCESTYPE – Method used to access the table.
MATCHCOLS – Number of index key used for index scan (when ACCESTYPE is I, IN, M, MX).
ACCESSNAME – Name of the index used for index scan (when ACCESTYPE is I, IN, M, MX).
INDEXONLY – Indicates if the index alone is enough to carry out the step.
PREFETCH – Indicates if data pages can be read in advance by prefetch.
COLUMN_FN_EVAL – Indicates when aggregate functions are evaluated.

Step Four:
Analyze the results using the following tips:

Is data accessed through an index?

ACCESSTYPE:

• I – Index. This is the best access after the one-fetch index. It uses the index to retrieve rows. The number of index columns used for matching is represented in MATCHCOLS.
• I1 – One-fetch index access. Is the best access possible as it requires retrieving only one row. However, it applies only to statement with a MAX or MIN function.
• N – Index scan with IN keyword in the predicate. In the example: T(IC1, IC2, IC3, IC4). Command: Select * from T where IC1 = 1 AND IC2 (in 1,2,3) AND IC3 > 0 and IC4 = 1. MATCHCOLS will be 3 and ACCESSTYPE will be N. The IN-List scan will be performed as three matching index scan: (IC=1, IC2=1, IC3>0), (IC=1, IC2=2, IC3>0) and (IC=1, IC2=3, IC3>0). If parallelism is supported they will execute in parallel.
• MX – Multiple index scan. More than one index is used to access a table. It is an efficient access path when no single index is efficient and a combination of index provides efficient access.
• R – Table space scan. This is the worst type of access as the entire table will be searched to process the query.

MATCHCOLS
The number of index columns matched on an index scan.

• If it is 0 all index keys and RIDs are read.
• If one of the matching predicates is a range there will be no more matching columns. Example for the index on T(IC1, IC2, IC3, IC4) for the following command the IC3 predicate won’t be used: Select * from T where IC1=1 and IC2 > 1 and IC3 = 1. The position of the columns in the index is used to decide that IC3 won’t be used.

INDEXONLY
If the columns needed for a SQL statement can be found in the index DB2 will not access the table. INDEXONLY performance is very high.

PREFETCH
Prefetching determines in advance if a set of data pages is about to be used and then reads the entire set into a buffer with a single asynchronous I/O operation.

• S – Sequential prefetch: data pages read in advance are accessed sequentially. Table space scan always uses sequential prefetch.
• L – List prefetch: one or more indexes are used to select the RIDs list in advance.
• D =- Dynamic prefetch: the pages to be accessed will be non sequential.
• Blank – Prefetch not expected.

SORTs
They add an extra step to the accessed data.

• METHOD=3 – These sorts are used for ORDER BY, GROUP BY, SELECT DISTINCT or UNION.
• SORTC_UNIQUE, SORTC_ORDERBY, SORTC_GROUP_BY – Indicates an extra sort for an UNIQUE, ORDER BY and GROUP BY clause.

In order to create necessary tables, it’s better to run

CALL SYSPROC.SYSINSTALLOBJECTS(‘EXPLAIN’,'C’,NULL,CURRENT SCHEMA)

Appending to ISPF Clipboard

Scenario:

I have a dataset with 10,000 lines. I want to cut the first 10 lines and last 10 lines and paste into another dataset. When I cut the first 10 lines and then again the last 10 lines, only the last 10 lines are pasted into the new dataset.

Is there anyway out (other than doing a 2 cut & paste)?

Yes, here it is.

1. First cut 10 lines, then issue CUT APPEND.


2. Cut last 10 lines, then issue CUT APPEND.


3. When you PASTE it, you got both.

Viewing ISPF Clipboard

When I issue CUT , I know that the CUT content are placed in a clipboard. And when I issue PASTE, the clipboard content are pasted.

But is it possible for me to view/edit the clipboard ?

One can view the clipboard after any valid CUT command was issued.

To view the clipboard, issue : CUT DISPLAY.

Clipboard manager will pop up and gives us options to edit or browse the content.

Changing the Create or Update User ID

Usually the PDS or PS bears the ID of the creator or the ID of the person who modified it recently. It's possible to 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

Repeating the ISPF command without typing again

Any command entered in the ISPF COMMAND LINE disappears after the successful execution of its intended function. If you want to repeat the same command , you got to re-type it or use some PF key to retrieve the last command entered.

But here is a cool method the make the command entered not to disappear and stay on the screen.

Precede commands with ‘& ‘

For Example:

&C    ‘110-PARA’      ‘220-PARA’

After the execution of the command, the below command stays on the screen.

This way you can entering the same command or modifying the command a little and using it multiple times.

Copy a Dataset using File-Aid

The steps to copy a file using File-Aid are :

1. Go to TSO/ISPF


2. Open fileaid giving command as per your shop’s configuration


3. Once FileAid’s Primary Option Menu is opened


4. Choose Utilities


5. Choose Copy


6. Give source PDS/dataset name (in FROM ) you might need to provide volume serial number if it is not cataloged.


7. Give destination PDS/dataset name (in TO) you might need to provide volume serial number if it is not cataloged.


8. Choose the Disposition New if your TO is new dataset OLD for existing dataset


9. Specify the properties of new dataset in “Allocate New SMS Dataset” It has to be similar to soure (record length/size/etc


10. If you are copying form a PDS then it will ask which all members you want to copy to destination.


11. Select members you want to copy and then hit return to confirm the members you have selected.


12. PF3 out and see the message top right corner for the status of your operation.

Recently accessed Datasets

Here is the way to find the last 10 datasets that you have accessed.

1. GO TO ISPF 3.4 option.


2. On the top, there is a MENUBAR. Select REFLIST


3. Select Option 1 in it.

Using this option you can find out the last 30 datasets that you have accessed.

Verification of DB2 Consistency token

When a Job abends at a program due to bind error with SQL code of -805 or -818 then generally there is issue with consistency token (often referred as CONTOKEN). These kind of abends can be easily fixed if we can find where the CONTOKEN mismatch occurs.

STEP1:- CONTOKEN in Package

RUN the following query

SELECT NAME, CONTOKEN, COLLID, PDSNAME FROM SYSIBM.SYSPACKAGE
WHERE NAME='MODULE'
AND COLLID='COLLID'

Provide the name of the module in "NAME" and Collection name in "COLLID"

In the Query output dataset locate the CONTOKEN value and view it in HEX mode (by issuing HEX ON command)

Lets assume the contoken in the above example to be as

1DE6102A

8A19F405

which is

18DAE1691F0420A5

STEP2:- CONTOKEN in DBRMLIB

The "PDSNAME" field above will indicate the DBRMLIB used for binding the module.

Goto the DBRMLIB

Open the DBRM for the above module, it will have the same name as the module name.

Search for the contoken in hex mode by entering the following command

F X'18DAE1691F0420A5'

(The CONTOKEN in the DBRM is generally at the 25th position as shown in the figure above)

 Note:- 

If the CONTOKEN in Step1 and Step2 does not match then Run the Bind job to fix the inconsistency. Bind Job is going  to update the CONTOKEN in the SYSIBM.SYSPACKAGE Table.

STEP3:- CONTOKEN in loadlib

Since the date and time part of the CONTOKEN are swapped in loadlib, you will have to split the CONTOKEN found in SYSPACKAGE table in two halves of 8 chars each and then swap their positions.

First Half
18DAE169

Second Half
1F0420A5

Swapping positions and joining them back will result in the CONTOKEN

1F0420A518DAE169

Go to the load library where the load to be checked is present.

CONTOKEN in Composite load of the link

Open the composite load and

search for the reversed CONTOKEN in hex mode in the composite load

F X'1F0420A518DAE169'

Note:-

If its found, no problem and if not then you need to process the link to fix the issue.

CONTOKEN in load of the program

Open the load object for the program, it generally has the same name as the module name.

Now search for the reversed CONTOKEN in hex mode in the load of the program using command

F X'1F0420A518DAE169'

Note:- If the CONTOKEN is found then its fine and if not then you will need to recompile and also relink(if composite load also doesnt has this) to fix the issue.

Source: Mainframe Wizard

COBOL Calendar & Date Functions

You need to know what date is 150 days from today (and this kind of stuff happens more often than you'd think)? Convert today to an integer date, add 150 to it and convert it back. No more checking which months you're going through to see if it's a 30 day or 31 day month. No more leap year calculations.

Some sample COBOL Date Example:

01  WS-TODAY         PIC 9(8).
01  WS-FUTURE-DATE   PIC 9(8).
....
MOVE FUNCTION CURRENT-DATE (1:8) TO WS-TODAY.
COMPUTE WS-FUTURE-DATE = FUNCTION DATE-OF-INTEGER 

(FUNCTION INTEGER-OF-DATE (WS-TODAY) + 150)

Probably the most useful intrinsic function is CURRENT-DATE which is a replacement for ACCEPT DATE and ACCEPT TIME. CURRENT-DATE is Y2K-compliant, having a 4-digit year. This function returns a 20-character alphanumeric field which is laid out as follows:

01  WS-CURRENT-DATE-FIELDS.
05  WS-CURRENT-DATE.
10  WS-CURRENT-YEAR    PIC  9(4).
10  WS-CURRENT-MONTH   PIC  9(2).
10  WS-CURRENT-DAY     PIC  9(2).
05  WS-CURRENT-TIME.
10  WS-CURRENT-HOUR    PIC  9(2).
10  WS-CURRENT-MINUTE  PIC  9(2).
10  WS-CURRENT-SECOND  PIC  9(2).
10  WS-CURRENT-MS      PIC  9(2).
05  WS-DIFF-FROM-GMT       PIC S9(4).

So not only can you get the time down to the millisecond, but you can get the difference between your time and Greenwich Mean Time.

The function is used in a MOVE:

MOVE FUNCTION CURRENT-DATE TO WS-CURRENT-DATE-FIELDS

The other intrinsic date functions deal with converting between either Gregorian dates or Julian dates and an internal Integer format. This Integer format is simply the number of days since some predetermined, fixed date like 1/1/0001. These four conversion functions are:

* Convert from Gregorian to Integer formats
COMPUTE WS-INTEGER-DATE = FUNCTION INTEGER-OF-DATE (WS-DATE)

* Convert from Integer to Gregorian formats
COMPUTE WS-DATE = FUNCTION DATE-OF-INTEGER (WS-INT-DATE)

* Convert from Julian to Integer formats
COMPUTE WS-INTEGER-DATE = FUNCTION INTEGER-OF-DAY (WS-JUL-DATE)

* Convert from Integer to Julian formats
COMPUTE WS-JULIAN-DATE = FUNCTION DAY-OF-INTEGER (WS-INT-DATE)

All Gregorian and Julian dates are expected to have 4-digit years.

COMPUTE is OK because we're only using integers here.

How many days between two dates?

COMPUTE WS-DAYS = FUNCTION INTEGER-OF-DATE (WS-DATE-1) -
FUNCTION INTEGER-OF-DATE (WS-DATE-2)

Converting between Gregorian and Julian formats used to be a pain also. Now:

COMPUTE WS-DATE = FUNCTION DATE-OF-INTEGER (FUNCTION
INTEGER-OF-DAY (WS-JULIAN))