Null Indicator In DB2

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Hi All,

1)Can anyone explain null indicator in DB2.
2) I want to delete multiple rows in DB2 table. Will I be using Cursor Declare,open,fetch and close, is this fine.

Thanks,

Ajay Baghel · Posted: Thu Jan 10, 2008 8:25 pm

Hi Kumar,

Null indicator variables are used in DB2 to -

1. Retrieve null values ( in case the coulmn is not defined in the table definition as NOT NULL, then it may contain nulls for some records. In such case, we should provide Indicatior variable for this column field).
2. For error handling ( to detect arithmetic expression errors and data conversion errors)

DCLGEN has a feature that generates the indicator host structure automatically. Any -ve value in indicator variable indicates null value in the corresponding host variable. A variable of -2 indicates arithmetic expression errors and data conversion errors.

Thanks,
Ajay

stodolas · Posted: Thu Jan 10, 2008 8:56 pm

You don't need a cursor if you just want to delete rows

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Hi,

Thanks for null indicator explaination.

How do i delete multiple rows from the table by without using cursor option.

My plan is to in the program
1)Select the rows by using declare cursor.
2)open cursor.
3)Fetch cursor rows :into.
4)Give delete query as you mentioned.
5) close cursor.
Is this ok.

Thanks.

dinucpaul · New User Joined: 11 Jan 2008 Posts: 3 Location: Bangalore

Hi Kumar,

If you want to delete multiple rows , its always better to use REORG with DISCARD option. Since normal delete will do lot of logging activity and will screw up the system.

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Can you let me know how do i use Reorg and Discard option to delete multiple rows. Since I have not used this. If possible could you give me an example code.

Thanks,
Kumar.

ahalyah · New User Joined: 13 Dec 2007 Posts: 25 Location: india

[quote="Ajay Baghel"]
Hi Ajay,

A variable of -2 indicates arithmetic expression errors and data conversion errors.

Data conversion error means what and how the column will converts into null due to this error? Can you explain little bit more on this?

Ajay Baghel · Posted: Fri Jan 11, 2008 4:18 pm

Hi Ahalyah,

For eg:

EXEC SQL
SELECT C1 * C2 INTO :VAR1 :I-VAR1
FROM EMP
WHERE EMPID = :EMPNO
END-EXEC

Here VAR1 and I-VAR1 are host and null indicator variables defined in the cobol program. If the multiplication of C1*C2 overflows the storage capacity of VAR1, then this is a data conversion error. Result of the expression will be null. Host variable's value will remain unchanged and
-2 will be posted to null indicator variable I-VAR1.

Hope it makes everything clear to you.

Thanks,
Ajay

ahalyah · New User Joined: 13 Dec 2007 Posts: 25 Location: india

Hi Ajay,
Thanks for your explanation.

stodolas · Posted: Fri Jan 11, 2008 6:22 pm

kumar1234:

How do you plan on fetching the rows with a cursor and into? Are you using a simple WHERE and deleting all the rows you are retrieving? If so you are wasting CPU time and your own time coding that.

If you are doing something like SELECT blah FROM foo WHERE bar = value. Then cursoring through that for a DELETE. Just do what I said earlier. Get rid of the stupid cursor and just do DELETE FROM foo WHERE bar = value. That does a delete without using a cursor, just as you asked for. On the other hand if you are doing logic and each row to see if it really needs to be deleted, then you need to cursor through them and then delete based on your checks.

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

I am first selecting the rows using cursor and then deleting the selected rows using normal delete query.

Thanks,
Kumar.

stodolas · Posted: Fri Jan 11, 2008 7:23 pm

Your select is a waste then. You asked if it was possible to do without a cursor. I have given that answer as yes twice now and a cobol example once.

kumar1234 · New User Joined: 06 Nov 2007 Posts: 84 Location: bangalore

Now I am planning to do with cursor. could you just guide me on that.

Thanks,

stodolas · Posted: Fri Jan 11, 2008 7:59 pm

What advantage do you see in using a cursor for the delete? It will just cause more CPU overhead and it wont help anything. If your SELECT cursor has a lock on the row. Your delete will fail.

Ajay Baghel · Posted: Sat Jan 12, 2008 5:13 pm

In case of the positioned deletes, you first declare a cursor CUR1 on your select query.

Then after fetching the record, give the below statement to do positioned delete:

EXEC SQL
DELETE FROM tblname WHERE CURRENT OF CUR1
END-EXEC

-AJAY