View previous topic :: View next topic
|
Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi,
i am executing a query like
Code: |
delete from testy.department |
I want to display the records that being deleted by this query.
Like
Quote: |
select * from (delete from testy.department); |
When i executed a query like this it gave me an error
Code: |
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD FROM. TOKEN
FULL LEFT INNER RIGHT WAS EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 506 0 0 -1 23 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001FA' X'00000000' X'00000000'
X'FFFFFFFF' X'00000017' X'00000000' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
RETCODE = 8 |
So, how to do it?
Sushanth Bobby |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
sorry to disillusion You,
what has been deleted has gone
if You want to know what You are deleting,
You must get the info before deleting |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Enrico,
I came across a query like this,
Code: |
Select id from final table(insert into uId1.account(name,type,balance)
values('Master Card','Credit',50000)) |
Using this query i can obtain the ID(identity column) when insertion happens.
So, i thought this may be also possible for DELETE too.
Sushanth |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Sushanth,
slick! Thanks for making me aware of something new (to me).
but, you did not bother to read everything. In the event that you did not
read this link:
www.databasejournal.com/features/db2/article.php/3342211
at the bottom there is a little matrix:
Code: |
Data-change Intermediate Result Table Type
Operation OLD TABLE NEW TABLE FINAL TABLE
INSERT no yes yes
UPDATE yes yes yes
DELETE yes no no
|
which indicates that you need to reference OLD TABLE and not FINAL TABLE when you are using
The data-change-table-reference clause in DB2 UDB Version 8.1.4. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
sorry for my previous brain check
thank You Dick for the pointer |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Thank You DINO,
I am just learning. For me insertion is working in this style.
But, delete is not working
Code: |
SELECT DNAME
FROM OLD TABLE ( DELETE FROM HXSULL.DEPARTMENT
WHERE ID = 33 )
; |
This is the error iam getting
Code: |
[b] DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD TABLE. TOKEN
FOR WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE WAS
EXPECTED
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 506 0 0 -1 23 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001FA' X'00000000' X'00000000'
X'FFFFFFFF' X'00000017' X'00000000' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
RETCODE = 8 [/b] |
Any ideas.........
Sushanth |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Enrico,
had I been a little quicker, I would have been the one saying NOOOOOOOOOOOOO can do.
We both should thank Sushanth for the a gentle reminder to continue to read not only documentation but the tech mags.
The more I read the article, the less suprised I was. The 'old table' 'new table' stuff is right out of db2 trigger terminology. |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Guyz,
Any clue, why it not working ?
Sushanth |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Sorry Sushanth,
I was editing my post as you posted your question.
? no idea. What version db2 are you running?
spent the last hour trying to find ibm documentation. will try again tomorrow.
will play tomorrow on my database (I have db2 9 on my pc, see if it works there). |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Sushanth,
both SELECT * and SELECT <column list> FROM OLD TABLE
(DELETE FROM qual.tablename WHERE ... <or without WHERE CLAUSE>
works great for me at home on DB2 UDB Vsn 9.
At work I have 7 in development and 8.? in production.
Can try there on Friday, when I go back to work.
The -199 means that after the FROM OLD everything goes into the toilet.
Now, have you actually been able to run a query using FINAL TABLE with an INSERT sub-select?
I noticed you used the word 'can' which indicates 'the ability to ...'
as apposed to
'have used' or 'was able to' which indicates success.
according to the documentation you need DB2 UDB V8.4.1.
I don't know if I have 8.4.1 at work, but since I have 9 on my pc,
I have no trouble with the syntax that you used.
That's one of the problems with databases;
all the good stuff is available on the next version your company buys.
If you don't know the release of db2 at your shop, ask your dba's.
Other Theme:
DB2 UDB V9 is free from IBM to download on your home computer.
Takes about 3 days with DSL 6000 (not really, but make some coffee or tea while you wait).
I have an old 3.1 CPU, 2 gig memory, (my pc is over 3 years old) and it runs great for testing sql.
I have written a couple of applications and, admittedly, it puts a load on my pc like rippen' videos.
Only had to invest in MS-Visual studio,
DB2 is free from IBM,
SQL server (i have that also) is free from MS,
and Micro-Focus Cobol is free, but you need MS-Visual Studio to run it. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10873 Location: italy
|
|
|
|
Quote: |
Only had to invest in MS-Visual studio, |
Hi Dick,
Visual studio is a big basket of ......
but some components are free,
Visual Basic, Visual c/c++, and many others
take a look at the ms website |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Dino,
I am using DB2 V8.1 in Development & Production.
Quote: |
The -199 means that after the FROM OLD everything goes into the toilet. |
So something wrong with the OLD TABLE keyword. But, FINAL TABLE is working.
Yes, executed the following query,
Code: |
SELECT *
FROM FINAL TABLE(INSERT INTO HXSULL.DEPARTMENT (DNAME)
VALUES('FIRE & SAFETY')); |
RESULTED SUCCESS!
Quote: |
That's one of the problems with databases; all the good stuff is available on the next version |
Yes absolutely true. u can say that again & again. I can tell you the worst stuff, i am on the road for preparing DB2 9 zOS CERTIfication. In my shop we just migrated to DB2 V8, which is right now in compatibility mode. So all the things, i think interesting, which i want to try like queries are not working. Because of the V.
DB2 VERSION : I asked my DBA, he told we are using V8 in DEVEL & PROD.
I am using platinum tool, so when i choose the sub-system to which i want to go in. It tells me the DB2 Version of the sub-system like DB2 V8.1
What iam interested to know is, are there any DB2 commands to know the version of the DB2 you are using. Like for instance in WINDOWS(You go to DOS prompt and type 'VER' it will tell you the windows version. LIKE THAT is there any command).
Other Theme : You are talking about the DB2 UDB V9 90-day trail version, i guess.
Wow, thats really a great news, Thank You. Till now i was think only DB2 Express-C is free.
[Just before typing this very words, i mailed to my DOWNLOAD MONSTER(that's a person off-course) to
look into this matter as a high priority red-one].
Quote: |
I have written a couple of applications and, admittedly, it puts a load on my pc like rippen' videos. |
I know ripping videos, so are you telling DB2 & MS VS jointly together is gonna choke my computer.
Quote: |
Micro-Focus Cobol is free, but you need MS-Visual Studio to run it. |
At room, i have COBOL3 & COBOL85 which executes in DOS. By using that use can learn COBOL fundamentals.
To write a cobol program, all i need is a editor, so you are saying i can write a cobol program, compile & link it, execute it and read the records from DB2 using MS VS. How ?. I know MS VS can work on languages like C,C++, VC++, C#, ASP.NET, VB, VB.NET. I have installed MS VS myself a lot of time. I have given an options to choose languages which i want to run. Never seen cobol there. Any additional ADD-INS or PACKAGES need to do that. hmm. I google for it.
I can arrange MS VS. Do i need VS 2003 or 2005
Thank You, Thank You and Thank You AGAIN!,
SUshanth Bobby |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
Who is Dino ? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hi Anuj,
dbzTHEdinosauer. . .
d |
|
Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006 Posts: 6250 Location: Mumbai, India
|
|
|
|
hff...ff...This was tricky ..much than "w.r.t.".. .
-Ad |
|
Back to top |
|
|
|