SQL (Insert/Delete) Testing ?


    Sponsored Links


  • 1. Security Auditing- Omit Entries
    We have installed some new software on our AS/400, and since, noticed a considerable increase in utilisation of disk. After some investigation, it appears that the software installed is posting many "Inter Process" (IP) entries into the security audit journal, and as a result, the security audit journal receivers are consuming lots of disk. All we need really is basic security auditing, changes to objects, user profiles etc. We're not interested in inter-process entries (not that I can see any use for them anyway). Other than turning security auditing OFF, is there any way of omitting the inter process (IP) entries from the system audit journal. Thanks in advance.
  • 2. SRC B1701001 and FSP ( service processor) problems on new machines ?
    On a 9406 520 Iseries V5R3M0 and latest cumulative and last month hipers ptfs applyed we have the following SRC: SRC B1701001 It seems that even IBM support has no clear knowledge about that code in its database But for the range it points to a failure of the service processor, so the replacement part is being sent. The point is that it will be the second time that on our new machine we have the service processor replaced in 2 months and we have the feeling that this new processor is introducing a tremendous source of inestability in an architecture that has allways been stable. Any comments or experiences? Thanks in advance
  • 3. API QusLspl total pages missing / total de pages manquant
    Hello, I am working with the API Quslspl and the format SPLF0300. The result is good EXCEPT for the spools created by a printer file with HOLD(*YES). The total pages field shows zo. If the spool has the status READY, I can see the total pages. If I release some "HLD" spool, then I can see the total pages. Afterwards, if I hold this spool, I can always see the total pages. Why does the total pages show zo? Thank you for your help. Rose-Noelle ------------------------------ Bonjour, Je travaille avec l'API Quslspl et le format SPLF0300. Le rultat est bon sauf pour les spools crs ?partir d'un printer file ayant le paramre HOLD(*YES). Le total de pages est ?zo. Si le spool a un statut RDY, je peux voir le total de pages. Si je libe un spool HLD, ensuite je peux voir le total de pages. Puis, si je suspends ce spool, je peux toujours voir le total de pages. Pourquoi le total de pages est ?zo? Merci de votre aide. Rose-Noelle
  • 4. CFINTJOB ?
    An interactive job on our system was looping taking a great deal of processing time on our AS/400. In this situation, a system job "CFINTJOB" appeared- what is this job and what does it do?
  • 5. AS400 FTP to FTP Server Write problem
    I have an FTP site on our W2K server (SP4) that I FTP files to from our AS400. To do this I need write authority, which is OK, but users accessing this site from a browser can upload/create anything on this folder. Is there a way in Internet Information Services, or the folder itself to stop users from writing to this folder yet still give me rights to write to it? Permissions on the folder, or on IIS doesn't seem to check the write privilages. Anyone know how I can accomplish this? Thanks.

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
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.


Similar Threads:

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



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

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

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 ?


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:

		STATUS_INDC = 'X')) = 1)


4.Index maintenance and INSERTs and DELETEs

(DB2 LUW v8x)


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?



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 45 guest