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))