Stupid SQL delete from logical file question

as400

    Sponsored Links

    Next

  • 1. supress CPC1163 from qsysopr
    Has anyone attempted to supress messages from qsysopr to another message queue or is there a way to eliminate them by msgid??
  • 2. Old as400 Twinax to DB9 cable.
    Hi, I collect old computers as a hobby. I've been given an old B series 9404 as400 box. I have an InfoWindow 3477 console, QSECOFR password and some twinax cabling, but I have no way to connect the twinax cables to the workstation controller card in the server nor the console. I believe I need something called a 4-Port twinax brick for the server side and a twinax T to DB15 cable for the terminal. The problem is they are hard to come by and when they show up on ebay the shipping usually costs 4 times as much as the part. Does anyone in this group have them? If you do could you measure which pins of DB15 and DB9 plugs go to which on the twinax connector? Alternatively, is someone willing to sell those parts for a reasonable price? I'm located in UK. Regards, Lucas

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