SQL (Insert/Delete) Testing ?

as400

    Sponsored Links

    Next

  • 1. How can i check wich obj was not restored??
    Hello. To test my backup , i had restored 1-2 LIB for tape in a TESLIB. with the command : RSTLIB SAVLIB(XXX) DEV(TAPE) RSTLIB(TESTLIB). i HAVE THE MSG: CPF3773...500 OBJ RESTORED .1 OBJECT NOT RESTORED............. HOW CAN I CHECK WHICH OBJ WAS NOT RESTORED AND ESPECIALLY WHY??? THANKS.
  • 2. Printing Problem
    Hi, I am using Client Access for Windows.... I have created a printer Session in one of the local client PC. While printing for the first time from any nodes First pages eject out after printing the first line. after that it becomes alright. . Can anyone help me out how to solve this problem. Tks, Vishu
  • 3. PM400 & Service Director through internet
    Anyone have any problems sending data via EXTRANET for these services.
  • 4. Referential & Check Constraint Error Handling in ILE Need Help
    Sorry for this posting all. I just found chap. 12 in the ILE RPG for AS/400 Programmer's Guide that deals with Exception Handling. I will read this and hopefully gain my knowledge.
  • 5. Referential & Check Constraint Error Handling in ILE Need Help
    I have so far never used Referential Integrity. I have been working through examples in the DB2 UDB (sg244249.pdf) guide. I can see that errors can occur. How would I handle a Check Constraint error in an ILE program? I'm getting blue in the face trying to find examples or text in an IBM reference guide of some sort to help me and so far cannot find anything. Anyone have suggestions of where to look to find info on how to handle errors in an ILE program? Is this something that I should just know? thanx in advance that 1 guy

SQL (Insert/Delete) Testing ?

Postby iseriesflorida » Sat, 18 Oct 2008 20:43:28 GMT

Since SQL is very powerful is there an easy way to simulate whatif(s)
if such SQL commands are ran against a PF.  My current work around to
this is copying files and then evaluating my results, I did not know
if there is an easiear way to do this.  I have used the apyjrnchg
command and rmvjrnchg command to move records back etc but did not
know if there is a quick and easy way to do this, thanks.


Re: SQL (Insert/Delete) Testing ?

Postby Dr.UgoGagliardelli » Sat, 18 Oct 2008 23:22:47 GMT

il 17/10/2008 13.43, Scrive iseriesflorida 43711712:
You can do with interactive SQL. Look at the help (pressing F1) of 
parameter  PROCESS of STRSQL command, or changing the same SQL session 
attribute from inside STRSQL (pressing F13 + 1)

-- 
Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAjoAlcoolInside
Spaccamaroni andate a cagare/Spammers not welcome/Spammers v merda
Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

Re: SQL (Insert/Delete) Testing ?

Postby Jonathan Bailey » Sun, 19 Oct 2008 00:06:10 GMT



Do you mean you would like to see how many records would be inserted/
updated/deleted ?
If so then changing the statement to a select would be the best way.
The next best & possibly quite poor is to ensure all your files are
journalled & start an sql transaction eg SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED
then rollback when you are done. This has the downside that other
processes can see your uncommitted chnages & possibly make decisions
based on them. The other downside is that the rollback is actually a
reverse update so it makes the physical changes again wheras commit is
quite a quick operation (apparently) STRSQL can prompt set transaction
for you. There is also the option to set COMMIT from the STRSQL
command or F13 option 1 & I am unsure as to the reason for this as it
dosnt make sql hold locks on inserted rows.
If you call stored procedures or have triggers you might find an
extraaneous commit or rollback which fouls up this testing.

Jonathan.

Similar Threads:

1.Insert select from SQL table function that modifies sql data

Hello.

v8.2.

Is it possible?
Example:
---------------
create function t(v varchar(1))
modifies sql data
returns table(c varchar(1))
begin atomic
return select v from sysibm.sysdummy1;
end@
declare global temporary table session.test(c varchar(1)) on commit
preserve rows@
---------------
Now I have tried:
---------------
1.
insert into session.test select c from table(t('1')) as f;
--- SQL20267, Reason Code=2
2.
insert into session.test
with a(c) as (select c from table(t('1')) as f)
select c from a;
--- SQL20165
3.
begin atomic
for g as
  with a(c) as (
  select c from table(t('1')) as f
  )
  select c from a
do
  insert into session.test values (g.c);
end for;
end@
--- SQL0901 (known bug)
---------------

Sincerely,
Mark B.

2.DB2 Audit - Insert, Update Delete Operations

I am working on DB2 Version 8.  Does db2audit have capability to
capture who, when, on what object an Insert, Update or Delete
operation was performed. I don't need the statement that was executed,
only the operation type and of course whether it was successful or
not.

There is no clear answer to this question in Version 8 documentation.
Some articles mention that CONTEXT capture these details however could
not confirm this.

3.Before update or delete trigger to insert ?

Hi,

I need some help...I'm fairly new to triggers and am attempting to
duplicate a trigger in DB2 that already works in SQL Server. The
basics are that I want to insert a row into an audit table using data
currently in a table BEFORE an update is executed. In addition, I need
to make sure the the record being updated is of a certain status so I
need to do a count on third table to ensure this status.
From what I've read, I can't do an insert in a BEFORE UPDATE trigger,
but I don't know where to go from here.  I also have to do the same on
a BEFORE DELETE. Any help would be greatly appreciated.

My trigger looks like this:

CREATE TRIGGER AUDIT_UPD_RBRT1
NO CASCADE BEFORE UPDATE ON RB.RT1
REFERENCING OLD AS OAUDIT
FOR EACH ROW MODE DB2SQL
WHEN ( (SELECT COUNT(*) FROM RB.RT_FLNG_ADPTN
	WHERE  (IDL = OAUDIT.IDL AND
		IDS = OAUDIT.IDS AND
		STATUS_INDC = 'X')) = 1)
INSERT INTO RB.RB_AUDIT_RT_DATA
	VALUES (OAUDIT.IDS,
		OAUDIT.IDL,
		CURRENT TIMESTAMP,
		1,
		11,
		OAUDIT.EXACT,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		OAUDIT.RATE,
		'TESTING');


Thanks,
Cindy

4.Index maintenance and INSERTs and DELETEs

(DB2 LUW v8x)

Colleagues,

I need to clarify something that I've seen inconsistent discussions
regarding. I'd like to know if for a given UPDATE or INSERT, will DB2
check and maintain only indexes defined on those columns that are in
the SET of the UPDATE or in the column list (implicitly or explicitly)
of the INSERT, or, if, no matter what, will all indexes be checked
when there's an INSERT or UPDATE against it? In other words, can DB2
use the data dictionary to go right to those indexes that need to be
modified, or will all indexes be checked ?

This gets down the age-old issue of if I want to define one more index
on a table, what will be the scope of the overhead--will overhead be
added to all INSERTs and UPDATEs against that table, or will it be
limited to just those statements that modify (change or populate)
columns that my new index is defined over?

Thanks,

--Jeff

5.Grant global select/insert/update/delete to all users

6. load from cursor vs delete/insert

7. update transmogrifies to insert/delete

8. Stupid SQL delete from logical file question



Return to as400

 

Who is online

Users browsing this forum: No registered users and 9 guest