View previous topic :: View next topic
|
Author |
Message |
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, |
|
Back to top |
|
|
Ajay Baghel
Active User
Joined: 25 Apr 2007 Posts: 206 Location: Bangalore
|
|
|
|
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 |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
Ajay Baghel
Active User
Joined: 25 Apr 2007 Posts: 206 Location: Bangalore
|
|
|
|
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 |
|
|
ahalyah
New User
Joined: 13 Dec 2007 Posts: 25 Location: india
|
|
|
|
Hi Ajay,
Thanks for your explanation. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
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. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
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, |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 631 Location: Wisconsin
|
|
|
|
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 |
|
|
Ajay Baghel
Active User
Joined: 25 Apr 2007 Posts: 206 Location: Bangalore
|
|
|
|
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 |
|
|
|