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

How to see the records that are being deleted


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

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Oct 07, 2008 5:41 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Oct 07, 2008 5:58 pm
Reply with quote

sorry to disillusion You,
what has been deleted has gone icon_biggrin.gif

if You want to know what You are deleting,
You must get the info before deleting
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Oct 07, 2008 7:05 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 07, 2008 7:45 pm
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Oct 07, 2008 7:57 pm
Reply with quote

sorry for my previous brain check icon_sad.gif
thank You Dick for the pointer
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Oct 07, 2008 9:07 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 07, 2008 9:11 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Oct 07, 2008 9:20 pm
Reply with quote

Guyz,

Any clue, why it not working ?

Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Oct 07, 2008 10:16 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Oct 08, 2008 2:42 am
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Oct 08, 2008 3:32 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Oct 08, 2008 11:44 am
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Oct 08, 2008 1:06 pm
Reply with quote

Who is Dino ?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Oct 08, 2008 1:26 pm
Reply with quote

Hi Anuj,

Quote:
Who is Dino ?


dbzTHEdinosauer. . . icon_wink.gif

d
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Thu Oct 09, 2008 8:00 am
Reply with quote

hff...ff...This was tricky ..much than "w.r.t.".. icon_sad.gif .

-Ad
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 Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top