SQL delete

as400

    Sponsored Links

    Next

  • 1. Disk utilization management and the IFS
    What sort of tools are available to track the utilization of disk space on the IFS at the file level? The RTVDSKINF command generates records with a type STMF that show sizes, but there is no file name or directory information.
  • 2. RUNSQLSTM Truncating Statements
    With Operations Navigator, I generated some SQL that creates a file. I dropped it into a source member (record length 112) and the source member is correct. When I execute a RUNSQLSTM, any statement over 75 characters is truncated and RUNSQLSTM fails. Can anyone tell me how to correct this behavior? I'm currently on v5.2. -Mike LaFountain
  • 3. Unable to start ftp on AS400 V4R5
    Hi all We have three AS400/iSeries and I can happily ftp between them all. So, when we bought a second-user 150 at V4R5, I didn't think it would be a problem to set it up. It is being run in green screen mode with the Basepak loaded. The company that sold the system to me wiped the disk and did a scratch install of Basepak and the cumulative ptf's. I've checked QUSRSYS and it is at V4R5 When I start host servers I get: - telnet snmp as-svrmap lpd 5001 as-cent > as-data > as-dtaq as-file as-netprt as-rmtcmd as-signon as-netd > as-tran > as-vrtp > If I try STRTCPSVR SERVER(*FTP) I get "FTP server starting" but it doesn't start. If I then try ftp 'xxx.xxx.xxx.xxx' which is to another AS400, where I know ftp works, all I get is: - Exception CPF3CDB occurred when trying to get information for exit point QIBM_QTMF_CLIENT_REQ with format name VLRQ0100. Now I haven't created any exit points, in fact I wouldn't know how to. QUSRSYS is definitely at V4R5. Does anyone have any ideas? Thanks, in advance for any help. Cheers Neil
  • 4. 1175 Character ID to Stream File?
    I have a customer that is using Character ID 1175 and needs to convert DBCS data to the IFS. I am having a hard time figuring out what stream file code page to use. Does anyone have any ideas on this? Thanks! Brad
  • 5. SQL CLI SQLAllocEnv
    Could anyone out there clarify the phrase in the IBM manual, "There can only be one active environment at any one time per application." Does this mean per activation or per job? Eg, would the following call trace have the desired outcome of having each connection's transactions isolated from each other, using different commitment definitions, or would PGMB ruin PGMA's day by freeing the environment upon return (running in server-mode)? This would be done in ILE RPG on V5R3. CALL PGMA (ACTGRP *NEW) SQLAllocEnv SQLAllocConnect [1] SQLConnect [1] ...transaction processing... SQLAllocConnect [2] SQLConnect [2] ...transaction processing... CALL PGMB (ACTGRP *NEW) SQLAllocEnv SQLAllocConnect SQLConnect ...transaction processing... SQLDisconnect SQLFreeConnect SQLFreeEnv RETURN PGMB ...transaction processing... SQLDisconnect [2] SQLFreeConnect [2] ...transaction processing... SQLDisconnect [1] SQLFreeConnect [1] SQLFreeEnv RETURN PGMA

SQL delete

Postby arrowcomputer » Tue, 13 Jul 2004 21:04:08 GMT

I am trying to delete records in one file based on criteria in a
second file.  I have the select running, and it select the proper
records.  I'm trying to figure the syntax of the delete.

The select :
select *  from SLALLO, MBADREP where ADAKDT > 1040731 and ADUUQ1
ADDCCD=ORTP and ADCVNB=ORDNO and ADFCNB=ITMSQ and ADHFCD < '50'     
order by ADCVNB   

The ADxxxx fields are from file MBADREP, the others from file SLALLO. 
I want to delete the SLALLO records that meet the where criteria.

My first attempt was (but failed syntax) - I tried several versions,
but I'm missing something here.

delete from                                                   
SLALLO, MBADREP  where ADAKDT > 1040731 and ADUUQ1                 
ADDCCD=ORTP and ADCVNB=ORDNO and ADFCNB=ITMSQ and ADHFCD < '50'

Re: SQL delete

Postby Charles Wilt » Tue, 13 Jul 2004 22:03:57 GMT

Terence,

SQL can't handle what you want to do.  You can't do a join during a 
delete.  You will need to make use of a subselect with an "in" or 
"exists" predicate.  For example, say I want to delete all order detail 
lines for orders more than a year old.  Problem is the order date only 
appears in the order header file.  So I'd need to to the following 
subselect:

delete from orderdtl
where ordernbr in (select ordernbr from orderhdr
                   where orderdate <= current_date + year(1)
                  )


exists is a little less straight forward.  At least for me as I don't 
use it often.  I think the following is correct:

delete from orderdtl A
where exists (select * from orderhdr B
              where orderdate <= current_date + year(1)
                and A.ordernum = B.ordernum
                and A.key2 = B.key2
             )


HTH,
Charles



In article < XXXX@XXXXX.COM >, 
 XXXX@XXXXX.COM  says...

Re: SQL delete

Postby LH » Tue, 13 Jul 2004 22:35:32 GMT

Terence I think it is possible.
Please make a good back-up (as always :)  of both files and try this:

Delete from SLALLO
where exists
(select  * from MBADREP
where   SLALLO .CONO = MBADREP .ADAENB
and  SLALLO.ORTP = MBADREP.ADDCCD
and SLALLO.ORDNO = MBADREP.ADCVNB
and SLALLO.ITMSQ = MBADREP.ADFCNB
and SLALLO.ITMSQ = MBADREP.ADFCNB
and  MBADREP.ADUUQ1 > 0
and MBADREP.ADZ93N < ADUUQ1
and MBADREP.ADA3CD  like '4%'
and MBADREP.ADHFCD < '50' )



Good luck
Loes



Re: SQL delete

Postby arrowcomputer » Wed, 14 Jul 2004 04:10:22 GMT

Thanks.   The "where exists" is the part I was missing.






Similar Threads:

1.Stupid SQL delete from logical file question

In DB2/400, if I run this statement:  delete from ordvcrec a where
not
exists(select * from i$paint b where a.part#=b.i$part)

Where ORDVCREC is a join logical of physical files ORDVALID and
ORDMASCREC, will the rows being deleted be the "logical" rows from
ORDVCREC only, or could physical rows (records) be wiped out in
ORDVALID or ORDMASCREC?


I am assuming that only logical rows will be deleted from ORDVCREC,
but I am afraid to try it...


Thanks for any and all opinions and answers...

2.SQL (Insert/Delete) Testing ?

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.

3.SQL : to delete spaces in a string

4.SQL RPGLE cursor delete from view? -204

real world app with SQL (did SQL in school but never had to used it)
done SQL in excel macros and net.data, but never needed to manipulate
the data
and most have been "select * from file with status = 'A'"...simple
so...now i am going complex

i have a huge select statement joining 6 files, with some selection
once i read it in, i need to delete a row if it meets a certain type,
then read the previous row and delete it also.

heres the code
i have taken variable names out, shortened some code(to protect the
innocent)

my problem is the delete, i get a SQLCOD = -204 in which the 
DSPMSGD RANGE(SQL0204) MSGF(QSQLMSG) is a whole lotta help

C/exec sql                                                         
C+ declare C1 dynamic scroll cursor for                  
C+ select t1.f1,t1.f2,t1.f2 ...                                       
C+ as   tview                                                      
C+ from CSHDET T1, CSHSUM T2, INVMST T3, CUSTH T4, CUSADRL1 T5,    
C+      MSTZEM T6                                                  
C+ where t1.f1 = t2.f1    and                          
C+       t1.f2 = t2.f2     and                          
C+ ....
C+ order by f1, f2...
C/end-exec                                                         

C/exec sql  
C+ open  C1 
C/end-exec  

C                   dou       SQLCOD <> 0  

C/exec sql                  
C+ fetch C1 into :f1,   
C+          :f2,         
C+          :f3,        
C+ ...
C/end-exec                  

C                   if        SQLCOD =  0                             
C                   if        f3 = '11'
                                                                      
C/exec sql                                                            
C+  delete from tview where current of C1                             
C/end-exec                                                            

C/exec sql                          
C+ fetch PRIOR from C1 into :f1,
C+          :f2,                 
C+ ...
C/end-exec                          

C/exec sql                                                            
C+  delete from tview where current of C1                             
C/end-exec                                                            

C                   endif  
C                   enddo  

C/exec sql                 
C+ close C1                
C/end-exec                 


initially i had it just selecting and printing records out just so i
could get the SQL RPG concepts down, then i added in the deleting

i think i am not understanding the "view" and "cursor" 

thanx in advance

5.SQL on Fire - Mass Deleting Data.

Linux Db2 LUW V8 FP 15

I was readiong Serge SQL on Fire presentation (part 2), and there is
a slide that shows how to mass delete data using procedures. Something
like:

loop: LOOP
DELETE FROM  (SELECT 1 FROM Table WHERE InvDate <= dt   FETCH FIRST
1000 ROWS ONLY) AS D;
IF SQLCODE = 100 THEN LEAVE loop;
END IF;
COMMIT;
END LOOP loop;

I have studied the access plan of the statement above, and compared it
to the plan of the statements my application runs (a simple 'delete
from table where ID=? and timestamp between ? and ?').

The plans are exactly the same, with the same Total Cost.

Question: Should I expect delete performance improvement if I use
procedures with Fetch First X rows, like Serge example ?  How does
this compare to single DELETE statements, in terms of performance ?

Thanks in advance.

-Michel.

6. * * * Important Please Delete * * * ( 666 asterisks deleted )

7. catch sql exceptions in sql procedure

8. exec more than 1 sql statement within an sql procedure handler



Return to as400

 

Who is online

Users browsing this forum: No registered users and 12 guest