Fixing DB2 Index Errors

Incident: Index error when DB2 loads a table.

Steps:

1. REORG INDEX - this was successful but did not fix index error
2. REORG TABLE - UNLOAD went ok, LOAD was had error
3. DROP INDEX - failed because index is used as PRIMARY KEY
4. ALTER TABLE and DROP PRIMARY KEY - successful
5. DROP INDEX - successful
6. LOAD TABLE REPLACE - successful
7. Defined VSAM data set for INDEX
8. CREATE INDEX - index error
9. Sorted unload records and found there were duplicate records. How these got there is anyone's guess.
10. SORTED records and removed duplicate records
11. LOAD TABLE REPLACE again using file with unique records
12. CREATE INDEX - successful
13. ALTER TABLE CREATE PRIMARY KEY - successful

RACF User Profile Concepts

RACF User profiles contain information about the user. A user profile can be connected (please note the right term: connected, not defined) to several groups.

RACF Concepts

Compared to mainframe security implementation, Windows and *Nix security is very primitive. Resource Access Control Facility (RACF) is one of the many products that protect mainframe resources.

RACF aims to protect mainframe resources from unauthorized access. It sees every file, database, table, transaction, service as a resource and each of these can be protected by RACF. RACF can be set to protect all resources by default. That means unless specifically given, no one can access a resource.

SYNCSORT: SUM FIELDS

SYNCSORT is one of the more powerful tools on the mainframe. One of its features is it can sum up a specific column in your file based on some keys. Say you have a file that contains items sold and number of items sold per transaction as seen below:

Using SYNCSORT (or DFSORT) to Automate Mainframe Code Migration

My development team was involved in a project years ago that required migrating 200 Configuration Items (CI's) such as JCL, PROC, SYSIN Cards, Source code, DBRMLIB members, Loadlib members, not to mention running BIND Packages on DB2 programs. The scarier part is that the migration needs to be done in a day. Our installation does not have a tool to help us migrate CI's so everything had to be done manually.

z/OS Compiling Programs that Use DB2 or CICS

In our previous article: z/OS Compile Process, we talked of the basic Compile process. On the mainframe though, you can write programs that call other subsystems, like a database system (ie. DB2) or a transaction system (ie. CICS).

z/OS Compile Process

Programs are written in programming languages that humans can understand. However, the processor cannot understand these languages. These languages need to be translated into machine language to make them useful. The process of translating these programming languages into machine language is called Compiling.

ICEGENER More Efficient than IEBGENER

MVS users know IEBGENER is used to copy files. However, it can also be very slow compared to SYNCSORT. The good news is that SYNCSORT has ICEGENER. The JCL for this is similar to IEBGENER except that it is more efficient.

PL/1 DB2 Program ABEND S0C1

Technologies Used:

• PL/1 3.8
• DB2
• z/OS

Caused By:
An 0C1 ABEND is caused by an invalid instruction.

Normally, 0C1 is caused by writing to an unopened file. But this was strange because there were no files that had to be written and the only I/O was to DB2.

A few discussion with our technical guy pointed to PL/1 compiler not actually generating data areas of variables if they are not being used. For some reason, this messes up the program during execution. I was led on a wrong path because someone suggested that I had to initialize all variables in the PL/1 program. I thought that this was not solution because if it was, then a lot of people would have complained.

Another option was to look at the parameters during the compile. I added the INIT parameters during the compile and still no success. Tried the STATIC(FULL) option and still no success.

Left without any other option, I stripped off all variables and ran the program only with the SQL statement. The program executed the SQL statement without any problem. I then slowly added a bunch of variables until I hit the one that caused the ABEND.

Quite a pain to do, but was finally able to isolate the problem and more importantly, fix it.

Auditing RACF Using Microsoft Access or any SQL Database

Executive Summary

Aside from being difficult, auditing RACF requires special skills. It needs understanding of RACF concepts plus understanding of the other subsystems being audited. A comprehensive manual audit took four auditors two months to complete. This can make the audit report irrelevant by the time it is released because errors may have been corrected already - making the auditors look incompetent - or worse, security breach may have occurred without the auditors detecting it.