Stupid SQL delete from logical file question

as400

    Sponsored Links

    Next

  • 1. Problem w.r.t to the delimiter usage in AS400.
    Hi, We are using DSPJRN to extract the Journal data from AS400 into an OUTFILE. And using CPYTOIMPF, we are sending the data to an ETL tool. But before that we are using the following query Update MYOUTFILE set JOESD = SUBSTR(JOESD,1,JOENTL-125) || X'25' This we are doing because, when using CPYTOIMPF with *fixed & *lf as delimiter, this is truncating the data till last non blank byte due to which we are not getting some data when last field of the Journaled file has blanks. SO, by doing this & CPYTOIMPF we will be getting 2 "\n\n" as delimiters. So, my problem is now some times there is 2 \n\n in the journal data itself in JOESD field (This is due to the packed decimal fields in the data) & since we are identifying the END OF RECORD with \n\n on the ETL tool, it is creating the problem. So, does any one know some unique delimiter on AS400 which can't be encountered in any of the data. Please mail me. Regards, Vijay
  • 2. Copy spooled file from AS400 using VB
    I need to create a VB application that retrieves spooled files from AS400 and copy them to my PC. How do you do it with Client Access Express and VB? Do you have any sample codes? Thanks.
  • 3. Printing to PC printers?
    Is it possible to set up Printer Device as (*LAN) device so to send printer output directly to PC attached printer ? I mean, when I send something to that printer's output queue on iSeries I would like it to appear printing on PC-attached printer HP DeskJet, which is on the same network as iSeries. We have Infoprint Server (v5.1) installed, OS/400 is v5r1, and we tried to set up PSF config so that PDF files are going to remote writer output queue (i.e. queue PRT6122). So we created remote printer and added PSFCFG to its outq, but nothing actually happens, and message is appearing saying "Remote device rejected an attempt by the writer to open a connection." On PC server side is Windows Server 2003 with Deskjet 6122 attached. The printer does not have IPDS functionality. These are the actual instructions used to setup printer device and configuration: CRTDEVPRT DEVD(PRT6122) DEVCLS(*LAN) TYPE(3812) MODEL(1) LANATTACH(*IP) AFP(*NO) PORT(9100) FONT(11) FORMFEED(*AUTOCUT) PRTERRMSG(*INFO) MSGQ(QSYSOPR) ACTTMR(170) INACTTMR(*SEC15) PARITY(*NONE) STOPBITS(1) TRANSFORM(*YES) MFRTYPMDL(*HP560C) PPRSRC1(*A4) PPRSRC2(*A4) ENVELOPE(*C5) IMGCFG(*IMGA04) RMTLOCNAME('192.168.56.5') USRDFNOPT(*IBMSHRCNN) SYSDRVPGM(*IBMSNMPDRV) TEXT(*NONE) CRTPSFCFG PSFCFG(QGPL/PDF2PRT) PDFGEN(*SPLF) PDFDEVTYPE(*IP40300) PDFSENDER(*SPLFOWN) PDFOUTQ(QUSRSYS/PRT6122) TEXT('PSF configuration to print PDF') Any help? Damir --
  • 4. Problem w.r.t to the Journal data
    Hi, We are journaling some 13 files to a Journal with IMAGES option as *AFTER instead of *BOTH due to which we're not getting Journal data for the Deleted records - with JOENTT as 'DL' in the OUTFILE that will be created by the DSPJRN command. But we don't want to turn the *BOTH option, becuase this will occupy considerable amnt. of memory DASD utilization in the AS400. So, is there any alternative for this. We want to get the data for deleted records with *AFTER option. Is there any wayout for this, One alternative could be write an ON DELETE trigger & capture the data, but Triggers would prove expensive for this. So, we ruled out this option. Kindly reply me the solution that any one has at XXXX@XXXXX.COM asap. Thanks & Regards, Vijay
  • 5. odbc hangs from sql agent
    Hello, I have a connection that is hanging when making an odbc connection to an as400 from Microsoft's SQL Server agent. When I run this manually from the command line it works fine. I am logged in as the same account as the owner of the sql server agent. The cwblogon is successful, it is the job that hangs. Logs show that the cwblogon works as I get the echo. ODBC is set up as a system DSN, FYI. cwblogon AS400 /u USER /p PASSWD echo test C:\perl C:\Export\export_odbc.pl -h C:\ -e test_export.sql -d test.txt Any suggestions, Thanks, Marc

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