DB2 for zOS Index on Expression

DB2 for z/OS version 9 brought in a new feature called "Index on Expression". Yes, you can create indexes on frequently used expressions in your queries. For example, if you often do a SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', then creating an index on UPPER(LAST_NAME) will be useful.

Until V8, you can create index on just columns, not on expressions/functions etc. So, you would have created an index on LAST_NAME, but DB2 would have ignored it because when you use a function on a column, DB2 ignores index access. But starting in V9, you can create an index on UPPER(LAST_NAME) and DB2 will use this index.

Now, to the tricky part ...

I tried to create an index on UPPER(LAST_NAME), DB2 complained that I need to specify 'locale' name. After reading a little bit about 'locale names', I specified UPPER(LAST_NAME, 'En_US') and DB2 happily created the index. I also ran runstats on the table. However, when I did an explain on the query SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', DB2 didn't seem to pick up the newly created index. That puzzled me.

After reading a little further, I found that I need to specify the same 'locale' in the query too. After specifying the 'locale', I did an explain and DB2 picked up the index. This is the modified query:

SELECT FIRST_NAME WHERE UPPER(LAST_NAME, 'EN_US') = 'SMITH'

Keywords: DB2, z/OS, Index, Expression, Upper, Locale, function

Trace Master - CICS abend AEYB

My colleague was trying to debug a CICS program in TraceMaster (a Macro4 Prodcut). He got this message:

DFHAC2206 15:45:50 CICTTOR Transaction xxxx failed with abend AZI6. Updates to local recoverable resources backed out. DFHAC2261 System OPR sentmessage (sese code 0824089E). 'DFHAC2206 15:45:50 CICTOPR Transaction xxxx failed with abend AEYB. Updates to local recoverable resources backedout.'

CICS manual describes the condition for code "AEYB" as "INVMPSZ". This "occurs if the specified map is too wide or too long for the terminal." So I had him change the Screen Size on Trace Master profile from "2" for 24x80 to "3"for 32x80 and SAVEd the profile (this is important, Trace Master doesn'tsave the profile automatically).

That fixed the problem.

However, he started getting a "X PROG" after starting the debug session. We looked at the program source code and found what the problem was. Apparently TraceMaster doesn't like FREEKB option. It failed while executing this statement:

EXEC CICS SEND
FREEKB
CONTROL
CURSOR (EIBCPOSN)
END-EXEC.

We don't understand why the program is doing this to free the keyboard because the SEND of the map has the FREEKB option on it. This problem got resolved when we bypassed this statement while debugging in TraceMaster. This extra 'SEND CONTROL' command seems to be confusing it. To bypass this command, we set a breakpoint at this statement and then did a GOTO (PF13) to the exit to skip the SEND to get it to work.

Mainview Tips

Here are some tips if you are an user of Mainview for z/OS

• VIEWS command gives you a list of all the VIEWs installed/available
• Use CON xyza, where xyza is the LPAR name, command to connect to the xyza LPAR
• Use MSG msgnumber command to get an explanation on the Error Messages displayed by Mainview. e.g., msg BBMXC739
• Use CUST command to customize a VIEW
• Use ASU command to update all windows automatically at a particular frequency (similar to the "&n" command in SDSF)
• SYSSUM view shows how busy the LPAR has been in the prev intervals
• To bring up multiple windows, use HS (Horizontal Split) or VS (VerticalSplit) to split the screen as required
• Use CLO n, where n is the window number, to close a window
• Use Wn;MAX to maximize window number 'n'
• Use Wn;REST to restore the window to it's original size
• Type SCREENS to see all the user defined screens
• Use SCR screenname to jump to a user defined screen
• To create your own screen, format the window as desired (multiple windows connecting to different systems etc.) and then type SAVESCR screenname
• Summary VIEWs end with letter Z. e.g., JCPUZ
• Long Term History data VIEWs end with letter L. e.g., JCPUL
• If you are in a view like JCPUZ, you can scroll back and forth in time using the command TIME. Type TIME after getting into JCPUZ. Type 'TIME = = NEXT' to go to the NEXT interval and type 'TIME = = PREV' to go to the PREVious interval without changing the interval data and time. Also, you can assign them to PF keys.

SDSF POS Field

I used to sort the jobs by POS (position) field in SDSF. This puts all the jobs in ascending order by date and time except the jobs that are in input queue or that are currently executing - they always appear in the top.

However, we recently upgraded from z/OS 1.8 to z/OS 1.9 and I don't see it sorted this way any more and the POS field is not populated by any value either. So I asked my sys prog and she asked IBM.

IBM's answer is here:
"The difference your seeing is described by the HOLD data for APAR OA26013 now PE'd by OA27126. The difference in behavior on the panel has to do with a change in the way SDSF gets the jobs/data for the panel. Previously, SDSF read the data from spool which meant running thru the queues and calculating the POS field on its own. These POS values were calculated erroneously by SDSF, assigning a position value to jobs on all queues.

Starting in SDSF 1.9, we're using JES2 SSI calls to acquire the job information which returns the correct POS values assigned by JES2. The POS field on the SDSF ST panel only applies to jobs on the INPUT queue, that is, jobs in-and-awaiting execution, not to any other queues like OUTPUT, SPIN and HARDCOPY.

These changes were necessary to expand support for SDSF to include JES3 compatibility and is considered working as designed."

So, now I'm sorting my jobs by ST-DATE and ST-TIME which is not the same as sorting on POS but comes a little close to that. I miss my POS field :(