Stupid SQL delete from logical file question

as400

    Sponsored Links

    Next

  • 1. Looking for (Software) Solution *SPLCTL Authority
    Hello, The issue of needing to give (some) users the special authority *SPLCTL and the riscs involed in doing so has been discussed often. But nobody ever came up with a solution. So we in our compagny are think of building our own solution. A program that controls: a) which users can control (Start, Stop, etc.) certain printers. b) which user can control (Display, Change, Delete, Hold, Release, etc) spooled files on certain output queues, or from certain users. BEFORE DOING SO WE ARE WONDERING: Did somebody already build such a program? In our case we need to avoid employees of one the partment to start or stop printers of other departments. And avoid them to look into spooled files of other departments. Can anybody help us out? Greetings, Ron Koudijs
  • 2. Client Access Express print question
    We are running Client access with the latest service pack. We have been printing to HP 1100 and 1300 printers, as well as hp 4300 printers just fine, using HP4 as the printer type (I'm not at a pc running client access right now, so I cannot describe how we have the printer configured in more detail). Some of our facilities are now purchasing HP 1320 laserjet printers. When we plug those in to replace the 1300, we cannot print. Instead of pages of ascii text, we just get a form feed. We always install the printers with PCL6 drivers, so I am unsure of what may have changed between the 1300 and the 1320 to cause this behavior. Does anyone have experience making this printer work under Client Access? Please respond to the newsgroup, email responses will bounce... Thanks, Brad Holman Daybreak Venture, LLC.
  • 3. AS/400 Tape Backup
    Hi , To prevent a backup is being stopped i wonder if it is possible to mount another tape automatically in case the loaded tape is determined write protected. As this situation occurs there is a system warning generated where a reply should be entered. My question is if it is possible to run a CL-script which unloads the current tape and loads the next one instead of giving a reply to this message. With kind regards, E.Receveur

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