SQL (Insert/Delete) Testing ?

as400

    Sponsored Links

    Next

  • 1. DRDA JDBC Drivers
    All, Why does JDBC driver that implement DRDA such as HOBLink J-DRDA need to create sql packages on the AS/400 that you want to connect to? This JDBC driver apparently is a generic DRDA client. The first thing they indicate you should do is to bind the sql packages on the system that you connect to (They do provide a tool to do this). When doing a PRTSQLINF on these *SQLPKG objects they seem like very simple sql packages. Etienne
  • 2. Image catalogue authority - query for IBM
    Hi all This is more directed at the IBM staff who lurk in these newsgroups. I am curious - why do you need *SECADM authority to use image catalogues? It just seems odd to me as it doesn't seem like a security administration type function. Maybe I'm missing something but to my way of thinking, an image catalogue is a fancy CD drive with a nice disk changer attached. You don't need special authority to place a real CD in the system drive - you don't need any authority whatsoever really. I mean, the janitor could insert one if he/she so desired... So what's so special about image catalogues? It's a bit of a nuiscence really as I now have to create a program that adopts authority to allow our technical team to use these things.
  • 3. New entry models in Italy
    Anyone know the specifics on these? Price? Specs? Any likelyhood of being available elsewhere? Cheers Garry
  • 4. 36E to Client Access
    I need to access some 36 data files and copy them to a PC. When I was using S/36 I could use Client access to reach these files if I had them defined/Linked in a Data Dictionary. Does anyone know how to do this with 36E files ?

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