IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Null Indicator In DB2


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Thu Jan 10, 2008 7:46 pm
Reply with quote

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,
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 206
Location: Bangalore

PostPosted: Thu Jan 10, 2008 8:25 pm
Reply with quote

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
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Thu Jan 10, 2008 8:56 pm
Reply with quote

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

Code:

EXEC SQL
  DELETE FROM table1 WHERE value=1
END-EXEC
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Fri Jan 11, 2008 12:53 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dinucpaul

New User


Joined: 11 Jan 2008
Posts: 3
Location: Bangalore

PostPosted: Fri Jan 11, 2008 1:26 pm
Reply with quote

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.
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Fri Jan 11, 2008 1:55 pm
Reply with quote

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.
Back to top
View user's profile Send private message
ahalyah

New User


Joined: 13 Dec 2007
Posts: 25
Location: india

PostPosted: Fri Jan 11, 2008 2:20 pm
Reply with quote

[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?
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 206
Location: Bangalore

PostPosted: Fri Jan 11, 2008 4:18 pm
Reply with quote

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
Back to top
View user's profile Send private message
ahalyah

New User


Joined: 13 Dec 2007
Posts: 25
Location: india

PostPosted: Fri Jan 11, 2008 5:08 pm
Reply with quote

Hi Ajay,
Thanks for your explanation.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 6:22 pm
Reply with quote

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.
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Fri Jan 11, 2008 7:19 pm
Reply with quote

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

Thanks,
Kumar.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 7:23 pm
Reply with quote

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.
Back to top
View user's profile Send private message
kumar1234

New User


Joined: 06 Nov 2007
Posts: 84
Location: bangalore

PostPosted: Fri Jan 11, 2008 7:56 pm
Reply with quote

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

Thanks,
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 7:59 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 206
Location: Bangalore

PostPosted: Sat Jan 12, 2008 5:13 pm
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts how to eliminate null indicator value... DB2 7
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Adding 'ODD' and 'EVEN' indicator at ... DFSORT/ICETOOL 6
No new posts Cobol prog to put an indicator on the... JCL & VSAM 1
No new posts the system or user abend SF0F R=NULL COBOL Programming 0
Search our Forums:

Back to Top