Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Null Indicator In DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Null Indicator In DB2
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: 205
Location: Bangalore

PostPosted: Thu Jan 10, 2008 8:25 pm    Post subject: Reply to: Null Indicator In DB2
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: 632
Location: Wisconsin

PostPosted: Thu Jan 10, 2008 8:56 pm    Post subject:
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    Post subject: Reply to: Null Indicator In DB2
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    Post subject: Reply to: Null Indicator In DB2
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    Post subject: Reply to: Null Indicator In DB2
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    Post subject: Re: Reply to: Null Indicator In DB2
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: 205
Location: Bangalore

PostPosted: Fri Jan 11, 2008 4:18 pm    Post subject: Reply to: Null Indicator In DB2
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    Post subject:
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: 632
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 6:22 pm    Post subject:
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    Post subject: Reply to: Null Indicator In DB2
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: 632
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 7:23 pm    Post subject:
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    Post subject: Reply to: Null Indicator In DB2
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: 632
Location: Wisconsin

PostPosted: Fri Jan 11, 2008 7:59 pm    Post subject:
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: 205
Location: Bangalore

PostPosted: Sat Jan 12, 2008 5:13 pm    Post subject: Reply to: Null Indicator In DB2
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am
No new posts Converting NULL column into NOT NULL ... Raghu navaikulam DB2 5 Sat Aug 06, 2016 3:45 pm
No new posts Compare 2 files & write Indicator... nagendrapgupta SYNCSORT 5 Sun May 15, 2016 10:42 am
No new posts GetObjectArrayElement returns NULL Majid Hussain Java & MQSeries 2 Wed Mar 30, 2016 2:59 am
No new posts Query to select Null row on priority RahulG31 DB2 24 Thu Jan 21, 2016 5:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us