Stupid SQL delete from logical file question

as400

    Sponsored Links

    Next

  • 1. Execute multiple SQL statements
    Hi, Can I execute multiple SQL statements at once from my program via an ODBC connection? I'm programming in VB and the database resides on an AS400 system ...
  • 2. QLGSORT
    Hi there, I want to sort the output of a SFL and want to use API:QLGSORT. Has anybody any idea or example how to implement this API into a program to receive a sorted SFL? Thanks in advance
  • 3. Anyone heard the term "Extension File"?
    Has anyone heard of or used the term Extension File? I never have and someone today recommended that we use an "Extension File" over a File. I do not see any IBM references to "Extension File" so there must be another name for these....? that 1 guy
  • 4. AIX on i5 installation problem DVD-IOA/IOP 5702/2844
    Hi All, I am running an i5 partition on an i550, in which I created a virtual scsi, NWSD etc.. to create an AIX5.3 partition The creation of the AIX partition is succesful, nearly with all virtual resources, except 1, a physical 7212 DVD-Ram. Now I am trying to install the AIX partition from a CD loaded on the DVD-Ram, this SCSI device in connected to a 5702 IOA, which is under an 2844 IOP. And that is what is going wrong with the activation, not allowed to use an IOA under an IOP, but that bus is also used (shared) by i5, so it needs an IOP. How can I install the AIX partition, Can i NIM install/bootp through an Proxy ARPed, or are there any other options. Maybe I need an 5706 IOP-less card, not Virtual ? TIA Jan Willem
  • 5. Start WebSphere applications during IPL
    I'm looking for a way to have my WebSphere applications start on IPL. I found the option to have the HTTP servers to start but I've been unable to find the same type of thing for WebSphere applications. Any suggestions on were to look or how to do this would be appreciated. Thanks in advance, Dan Nelson

Stupid SQL delete from logical file question

Postby Old Fossil Bama » Tue, 15 Jan 2008 01:33:20 GMT

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


Re: Stupid SQL delete from logical file question

Postby CRPence » Tue, 15 Jan 2008 06:27:09 GMT

   As a Join Logical file [SQL VIEW, or DDS LF], will not be update 
capable.  Refer to DSPFD ORDVCREC to confirm that the database considers 
the VIEW to disallow update capabilities; i.e. a request to DELETE 
should fail with sqlcode -150 [refer to: DSPMSGD SQL0150 QSQLMSG]:

     Allow read operation  . . . . . :            Yes
     Allow write operation . . . . . :            No
     Allow update operation  . . . . : ALWUPD     *NO
     Allow delete operation  . . . . : ALWDLT     *NO

   Similarly the following statement can show the Delete Capability of 
the file:
    SELECT a. IS_DELETABLE , a.* FROM QSYS2.SYSVIEWS a
    WHERE  TABLE_NAME = 'ORDVCREC'

   The DELETE request will have to be against whatever Physical data 
file(s) [SQL TABLE, or DDS PF] from which rows should be removed.

   FWiW: variant characters in names should be avoided; i.e. although 
the three characters @ # $ are allowed, they should be avoided.

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer




Re: Stupid SQL delete from logical file question

Postby Old Fossil Bama » Tue, 15 Jan 2008 10:17:54 GMT


> llow write operation . . . . . :>o
> llow update operation  . .>. : ALWUPD *NO
> llow delete operation gt; .>. . : ALWDLT *NO
>
> imilarly the following statement can show>the Delete C>pability of
> the file:
> SELECT a. IS_DELETABLE > a.* FROM QSYS2.SYSVIEWS a
> W>ER> ABLE_NAME = 'ORDVCREC'
>
> he DELETE request will have to be >gainst whatever Physical data
> file(s) [SQL TABLE, or DDS PF] from>wh>ch rows should be removed.
>
> WiW: variant characters in names s>ould be avoided; i.e. although
> the three characters @ # $ are a>lo>ed, they should b> avoi>ed.
>
> Regards, Chuck
> --
> All comments provided "as is> with no warranties of any kind
> whatsoever and may not represent positi>ns, strateg>es> n>r >iews of my
> employer
>>
>
>


> > In D>2>400, if I run this stateme>t>
> > delete from o>d>crec a
> > where not exis>s>
> >  select * from i$pai>t >
>> > where a.part#=b.i$part )
>
> > Where ORDVCREC is a j>i> logical of physical files ORDVALID and
> > ORDMASCREC, will the row> >eing deleted be the "logical" rows from
> > ORDVCREC only, or cou>d>physical rows (records) be>wi>e> out in
> > ORDVALID or ORDMASCREC?
>
> > I am assuming that only l>g>cal rows will be deleted from ORDVCREC,
> > but I >m >fraid to try it...- Hide quoted text -
>

Thanks, Chuck.

Similar Threads:

1.Stupid delete logical 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.fulltext logical files offline and can't be deleted

Microsoft SQL Server 2005 - 9.00.2047.00 (X64)   Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition
(64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

I have a database with one data file, one log file and 2 bogus, offline
FT "files":

FileGroupName	FileGroupType	FileType	FileState
PRIMARY	ROWS_FILEGROUP	FULLTEXT	OFFLINE
PRIMARY	ROWS_FILEGROUP	FULLTEXT	OFFLINE

The physical path for both FT folders is not valid.  The DB is at now
at compatibility level 90, but it was at 80. When I try to do an alter
database remove/modify, I get an error:

"Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could
not be initialized."

How do i get rid of these bogus DB "files"?

TIA,

-Peter

3.create a Logical file question

Stupid question for the day...my brain is still out on summer
vacation.

I have some files at month end that I want to create copies of; two
physicals, one logical.  The logical is built over one of the
physicals.

For instance, at month end I now create two physical files:

CPYF FROMFILE(CURRCUST) TOFILE(PASTCUST) CRTFILE(*YES)
CPYF FROMFILE(CURRAREC) TOFILE(PASTAREC) CRTFILE(*YES)

then, I have dds to create a logical file over PASTCUST and it does
this by:

CRTLF(LOGFAREC) OPTION(*NOSRC *NOLIST) 
(the dds for LOGFAREC points to physical file PASTAREC)

Okay, I want to do an additional variation.

Now at each month end I want to create the two additional physical
files (where YYMM are current year/month):

CPYF FROMFILE(CURRCUST) TOFILE(CUSTYYMM) CRTFILE(*YES)
CPYF FROMFILE(CURRAREC) TOFILE(ARECYYMM) CRTFILE(*YES)

I have no problem accomplishing this.

My problem is, how do I another LOGICAL FILE over ARECYYMM, without
having to create another dds member, or can I? I wanted to just do
something like OVRDBF of the physical file and then use the CRTLF to
create a LF with a different name, i.e.

OVRDBF FILE(PASTAREC) TOFILE(ARECYYMM)
CRTLF(LARCYYMM) 
DLTOVR FILE(PASTAREC)

Since the dds points to PASTAREC, I thought maybe I could use an
OVRDBF before the CRTLF, but that doesn't seem to work.  Is there an
EASY way to creat a new source member on the fly that points to
ARECYYMM or is there an easier way (I know there must be) to do the
CRTLF?

thanks,
ga
George Applegate
 XXXX@XXXXX.COM 

4.Logical File/View question

We are having a real weird problem with logicals and views.
Every once in a while they will not pull the data they should.  But if
we recompile the logical or recreate the view, everything starts
working again.
Any hints or suggestion on where to start?  Or what the problem/issue
could be?
Thanks!!

5.Possibly stupid questions large tables/files, port 5432 access

6. stupid SQL question on Where clause

7. Stupid SQL question?

8. Eclipse & Microsoft SQL Server: Stupid question



Return to as400

 

Who is online

Users browsing this forum: No registered users and 29 guest