SQL Token -905 : You can't run any longer

My first -905, not a good feeling for a DBA - but yes, good feeling that I got this error for my blog post. I ran a DSNTEP2 job to perform a SELECT COUNT(*) on my TEMP_TABLE which contained about 10M rows for testing run times, I locked the session thinking it's going to take a while, by the time I resumed work - I got this beautiful error :

Resolution :

I need to contact the capacity management team to increase my ASU time limit. I rather opted to run the query on a table that contained 100K rows and completed my tests. But if this strikes your job, don't spend much time rerunning or trying to find a fix - contact your support staff who is responsible for increasing ASU time limits on your TSO ID.

SQL TOKEN -438 : Nightmare Error

We are not expected to remember the fix/resolution for each and every SQL error code we come across. Most of the error codes like -904, -805, -204 are common and we get to see them once in a week ? Most probably, yes ! But what about those error codes which you seldom encounter ? Manuals - SQL REFERENCE is an excellent manual to refer for SQL error codes. What if your error code resolution says - " Investigate on the application text " ? OK - You go investigate. What if your investigation shakes the very foundation of your knowledge on DB2 ?

Here is the error message from SQLCA :

Piece of cake, Column COL1 is not a part of the parent table TAB1. Now here is the scary part - COL1 was indeed a part of the TAB1 when I checked the catalog. Then why is the error text misleading me ? When I checked with the application development team I got some useful information as well. They pointed out the statement number which ended up with an -438.

Here is the SQL statement :

EXEC SQL INSERT
INTO TAB2
( COL1
)
VALUES
( : HOST-VARIABLE )

An insert query, anyways I had lost it as there was no problem with the table, column, statistics what so ever. I ended up shooting an email to my team asking for help. I was amazed when my team pointed out the problem. It was a TRIGGER that was defined on TAB2 that was causing this error.

CREATE TRIGGER TRIG1
NO CASCADE BEFORE INSERT ON APP1.TAB2
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
WHEN ( EXISTS ( SELECT COL1 FROM APP1.TAB1 WHERE COL2 = NEW.COL2 ) )
BEGIN ATOMIC
SIGNAL SQLSTATE 'APPOUT' ( ' COL1 DOES NOT EXIST IN THE PARENT TABLE TAB1 ')

The development team was contacted to correct the row being inserted into the child table ( TAB2 ).

The SIGNAL being passed from the trigger was misleading, if the SIGNAL SQLSTATE passed for a wrong insert was ' VALUE BEING PASSED INTO COL1 DOES NOT EXIST IN THE PARENT TABLE TAB1 ' , then it would have been better ? At least, it would not have made me upset for a while.

SQL TOKEN -904 : Easy fix for a deleted LDS

-904 ( Resource Unavailable - LDS is not found in ISPF 3.4 ) error code is pretty much common in development/test environments as the retention period ( CATALOG ) for unused application LDS is set to a low value ( 100 days for example ). So, If you don't use an application table for more than 100 days for your development/tests the linear dataset ( VCATNAME.TEST.DSNDBD.TESTDB00.TESTTS00.I001.A001 ) is automatically deleted ( you can approach your storage/media management team to find out how ). You might have an image copy presence on tape and SYSCOPY catalog table for the table - But until you get back your LDS, it will not serve any purpose. I am about to share an easy way of creating the LDS for fixing -904 for the above scenario i.e - if the LDS is deleted :

Submit a DUMMY LOAD REPLACE job on the table, this will create the LDS. Example step of an DUMMY LOAD REPLACE  :

//STEP1        EXEC DSNUPROC,UID='TEST.LOAD',UTPROC='',SYSTEM='TEST'   
//STEPLIB    DD DSN=ACCOUNT123.DB2.R.TEST.DSNLOAD,DISP=SHR      
//SYSREC   DD DUMMY   
//SYSUT1      DD DSN=HILVLQ.TABLE00.SYSUT1,                            
//                    DISP=(MOD,DELETE,DELETE),SPACE=(CYL,(100,50),RLSE)  
//SORTOUT   DD DSN=HILVLQ.TABLE00.SORTOUT,                           
//                    DISP=(MOD,DELETE,DELETE),SPACE=(CYL,(100,50),RLSE)  
//SYSIN        DD *
LOAD DATA REPLACE LOG NO NOCOPYPEND INTO TABLE APPLN1.TABLE00

Once the LDS is created, use the RECOVER utility to bring back your data using the image copy.

Note & Precaution : Production LDS datasets are retained permanently in most of the accounts as a business need, until and unless the application is decommissioned and the cleanup activity is initiated.  -904 is also caused due to other factors and the resolution may vary. Performing a dummy load replace on an existing cataloged LDS will cause complete data loss and can impact your application drastically.

SQL TOKEN -551 : Unable to create VIEWS

This post is especially for my readers who are new to the field of application database administration.  If you are mainly working with VIEWS in test/development/production and you as an application DBA's are responsible for granting appropriate access to the secondary authorization ID's then I am sure you would have come across requests for " granting access to create views " . I used to wonder what type of access is needed to create views ?

It may seem like a funny question if you already know the answer - but hey, there are guys out there who are new to these kind of requests you know.

OK - Here is the error message



All you need to do is grant SELECT access to the secondary auth ID ( APPLNID1 in this example) and boom !! Your application developer will be able to create views provided he/she is a part of the auth ID. You don't need to grant any other access unless and until the program performs DML activities on the views.

So now you know - You are not alone and you do have good help !!

SQL TOKEN -922 : Strange CICS error

-922 - Access Issue. !! Yes, we all know that the authorization ID is lacking a particular type of access on the program. Have you ever encountered a -922 with the auth ID having '' complete '' access over the program ? In fact the OWNER of the program is encountering a -922 - LOL ! Funny eh ? , Yes ! I did find it funny. Let's call it strange shall we ? You may like the word ' strange ' if you are amongst the hard-core technical blog readers.

Here is the -922 error message



Here are the access listings on this program

1) User Information



2) Access Information for the above user, by pressing the PF11 key

OLTAP01 is a CICS program and this error message is retrieved from SQLCA. We know that a program in the form of a package can only be executed with the help of a PLAN, Note how the plan name is pointing to ' DEFAULT '. When I searched in platinum I found out that this program was mapped on to plan named ONLINE1 and not DEFAULT.

The question that rushed into my mind was - why is ONLINE1 not showing up in the error message. Note the P.AUTH pointing to a number ' 402 ' - What this is all about ? I don't see a secondary or primary auth ID named 402 in the system.

I then realized that 402 was a reference number on the CICS screen and in fact the owner of the program was trying to run the CICS transaction. If that's the case - I wouldn't be worried about whether the number is 402 or 710. I narrowed my worry list to the plan - ' DEFAULT ' - I looked some advise from my system administration team, I told them that the ' ACCESS ' is in place and this can't be an access issue - nor I am willing to grant any access to 402, 710 etc. The next day I got the email stating - The issue was fixed !

It so happened that the RCT definition for this program from the CICS end was missing. The CICS administration team created an RCT entry for this program which fixed the issue. I am not a CICS guy, but if you want to read more about creating an RCT entry in CICS environment you can read more about it here.

Bottom Line :

Don't try anything more if you see the accesses are in place and yet your application team receives -922. Go one level up and ask your system administration. After reading this post - I guess you will directly ask your CICS administration team. Go Ahead, let me know if I suceeded in saving your precious time !

SQL TOKEN -303 : Program Invalidated

DB2 Views cannot be altered or modified, they have to DROPPED & RE-CREATED.

Considering the above fact, if a DB2 view linked with a DB2 program is dropped and re-created with new or existing set of columns - Go for an impact analysis report on the affected programs using DB2 Administration tools. The below example ( step-by-step ) shows how a program gets invalidated once the corresponding view is dropped.

1) Program APPL0001 access view VIEW0001

Using this method ( On CA-Plaitnum ) we can generate all the programs accessing the view.

2) View is dropped

DROP VIEW C00001.VIEW0001 ;
COMMIT;

3) Program requires a BIND operation to be performed

APPL0001 is invalidated and must undergo a REBIND or BIND to account for all the columns and host variables in the program DBRM. In case we miss to perform the BIND operation, DB2 tries to BIND the program during run time and points out the error if there is a column mismatch/unaccounted for.

4) An SQL code of -303 is generated due to the mismatch as the validity was not checked -



Similar SQL error code would have poped up on the screen while performing the BIND on the affected programs if the columns/host-variables were not accounted for.

Resolution :
Never drop a view if the programs are linked to it. Try creating a new view for your program. Views are logical - they don't hurt in anyway. As for the -303 : View corrections have to be rolledback and the abended program has to be bound again. Perform a PRECOMPILE and BIND in case there are changes in the source code.

SQL TOKEN -805 : Solve it like never before

I am not sure about you, but for me as a DB2 DBA this SQL code used to be a nightmare. Why a nightmare ? I didn't know where to look for things when I used to encounter emails having an -805 in the subject line. After working with lot of -805's I felt, may be there is someone out there who is still finding it difficult to solve them.

After reading this post, the perception on -805's may change !

Can I fix the -805  INSTANTLY for my application ?

YES ! You can. PRECOMPILE & BIND your program again - One stop fix for all -805 !, Tried, tested and executed - 100 % success ratio - guaranteed !! By doing so, you generate a new consistency token for your load and DBRM module. While performing this operation you need not worry about anything else if there are no changes in your job ( through which you run your program).

Where exactly the link is lost ?

Most of the developers perform PRECOMPILE while generating a new piece of code ( program ) I have seen my fellow developers using online tools to perform precompile ( it's pretty amazing ) and they overlook to UN-comment the BIND cards. PRECOMPILE should always be followed by a BIND step else you end up with -805. Based on my experience 80 - 90 % of -805's are caused since the BIND's are not performed.

I want to work on fixing the -805 error code as a DB2 DBA, Are there any thumb rules ?

-805's can be due to several reasons, BIND's issues, wrong DBRM name, no load module mapped to the application job, data sharing environments - there are loads of them why they occur !. Well, there isn't any such thumb rule but I have a set of standards I follow while working on -805's - it does apply pretty well !

Try following these steps

1) Don't Panic ! - All the -805's can be fixed

2) Copy the consistency token ( 191C3BF40947BC2E ), dbrm or package name ( PROG001 ) and Plan name ( PLAN001) from the error message ( SQLCA ) into a fresh note pad for analysis. Note the reason code as well, it will narrow down your search while you refer the SQL reference manual.

3) Find the DBRM library for the module ( PROG001) using the administration tools in your shop. In our case it is TEST.DBRMLIB

4) Browse the PDS - TEST.DBRMLIB, find for PROG001 member in the list of members displayed. Select PRG001

5) Find for the first 8 digits of the contoken ( it's a hex code ) by issuing a find command on the command line - F x'191C3BF4'

5a) If you find this consistency token - it indicates a BIND was performed on the program - One possible reason can be the PLAN ( PLAN001) is not having PROG001 as its package. Go to the PLAN  and include the member using PKLIST or member keyword and BIND the plan. But most of the times you won't find the contoken within the member.

5b) If you could not find the consistency token - it indicates the BIND was not performed, a different DBRM library was used - Generate a BIND card and perform the BIND or ask the developer for the DBRM library which he used while performing the BIND and perform step 5 again.

6) Cross check for load module concatenation in the Job ( JCL ) - Based on the concatenation pattern(s) the contoken(s) are matched with recurrence in DBRM modules. If there is a old load module concatenated at the beginning then * comment * the library or libraries out else there is a good chance that a wrong contoken may get associated with your load library

Now, try not to forward any -805's to your DBA's. Try finding out the cause for it and fix it yourself. I bet, fixing -805's is fun and gives some high dosage of '' learning experiences ", I used to run away from -805's during my early days as a DBA. Now, I anxiously wait for any -805's to creep in and enjoy fixing them ( yes, the developers here try to fix it themselves and then forward the email once they loose the battle ). Try following these steps and let me know if it worked out for you by leaving a comment, if it did not - leave a comment and I will update the post with more details.

NOTE : Performing REBIND's won't fix -805s !

SQL TOKEN -510 : Ambiguous & Unambiguous

I perform quite a lot of BIND's in my environments. Lately, I received an email with an error message pointing out to a BIND operation  -

BIND error

DSNX200I -TEST BIND SQL ERROR
USING TAUTH00 AUTHORITY
PLAN=PLAN0001
DBRM=PACK0001
STATEMENT=278
SQLCODE=-510
SQLSTATE=42828
TOKENS=
CSECT NAME=DSNXODML
RDS CODE=-530

Before we research into the error message, lets look at one of the BIND parameter - CURRENTDATA and we will also see the difference between Ambiguous & Unambiguous cursors

Assuming two applications try to access a set of tables at the same time -

If the cursor is read only ( FOR FETCH ONLY ) and the program resides on isolation level CS, this isolation level does not guarantee that the data retrieved will be stable.
If the cursor if updatable ( FOR UPDATE OF ) and the program resides on isolation level CS, this isolation level guarantees that the data retrieved is stable.

I guess I have found ways to guarantee stable data for read-only cursors -

CURRENTDATA(YES|NO) with Isolation level CS -

If you have a read-only cursor and you want to fetch stable data, use CURRENTDATA(YES) in the bind card. CURRENTDATA(YES) ensures data stability for read-only cursors. If you specify CURRENTDATA(NO) then, there is no guarantee that data retrieved is stable as another application would have updated the data before your application performs the commit.

Ambiguous and Unambiguous Cursors -

The cursor is ambiguous if DB2 is not able to determine whether it is used for update or read-only purpose. Lets look at one example :

STEP1 : DECLARE P01-CUR FOR SELECT * FROM PRDNSCH.VIEW001 WHERE P01_EMPNO = :HV-01
STEP2 : OPEN P01-CUR
STEP3 : FETCH P01-CUR INTO :FV-01, :FV-02, :FV-03
STEP4: CLOSE P01-CUR

DB2 will never know what's happening with P01-CUR. In STEP 3, FETCH operation is performed from the view, but the view is not set as read-only with help of FOR FETCH ONLY clause in STEP1.

Unambiguous cursors are the one's where DB2 clearly understands what the cursor is meant for. As an example :

STEP1 : DECLARE P02-CUR FOR SELECT * FROM PRDNSCH.VIEW001 WHERE P02_EMPNO =:HV-01 FOR UPDATE OF P02_EMPNO, P02_SALARY
STEP2 : OPEN P02-CUR
STEP3 : UPDATE PRDNSCH.VIEW001 SET P02_EMP =:FV-01, P02_SALARY =:FV-02 WHERE CURRENT OF P02-CUR
STEP4 : CLOSE P02-CUR

Now, DB2 knows that P02-CUR is a UPDATABLE cursor because of the FOR UPDATE OF clause specified in STEP1.

We now know the differences between ambiguous and unambiguous cursors along with CURRENTDATA, lets look at solving the -510 error code. Looking at the source code I discovered:

DECLARE clause for the cursor was defined as -

DELCARE P01-CUR FOR SELECT * FROM PRDNSCH.VIEW001 WHERE P01_EMPNO =:HV-01

The processing being performed by the cursor was -

UPDATE PRDNSCH.VIEW001 SET P01_EMPNO = :FV-01, P01_SALARY =:FV-02 WHERE CURRENT OF P01_CUR

It's a typical case of -510, DB2 could not make out that this cursor was updatable, since FOR UPDATE OF clause is not defined in the DECLARE CURSOR statement and CURRENTDATA is set as NO.

The developer then changed the DECLARE clause of the cursor and introduced the FOR UPDATE OF clause which resolved the -510 error code. Uff !! I am trying to improve this post on a constant basis and I admit this is a confusing topic !