Solved

Convert and Put OUTQ files to PC using VB6 if possible

Posted on 2006-06-22
52
1,710 Views
Last Modified: 2011-02-02
Hi,
I m a vb6 programmer. I have started a new job where they have main software running on AS400 and I have no previous knowledge of AS400. After trying I have been able to access the AS400 database using IBMDA400 through vb. The requirement is 1) I want to sort my MPRINT and the PRT03 outqueue. I donot know RPF or CL. 2) I want to be able to access these output queues and pickup the spoolfiles and store into sql server database for future reference and also to be able to print the reports using Sql server database. I have already tried using the Copy spool files command to physical file and then from physical file to pc document in QDLS folder. But i want to automate all this from say MPRINT output queue. So that what ever is there in MPRINT outqueue it should get transfered to the PC directory specified. I want to do it in vb6 but if it is better and easier to do in rpg or cl whatever then i would need the full steps as i donot know rpg or cl programming. We have V5R2 and I have iseries navigator installed on my pc.

will appreciate any help
Regards,
Harshu.
0
Comment
Question by:harshbala
  • 21
  • 20
  • 9
  • +2
52 Comments
 
LVL 14

Expert Comment

by:daveslater
ID: 16958960
Hi
from an as/400 programing point of view you can:

1. assign a dataq to an outputq, each time a released spool file hits the outputq an entry will be sent to the dataq.
2. This dataq can be accessed either my VB or and AS/400 RPG / CL program
3. The back-end program can then do the cpysplf to the database and transfer it to QDLS or anther IFS directory.

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 16959065
hi
look at this question to see about dataq and outq
http:Q_20714735.html
and
http:Q_20525743.html
0
 
LVL 13

Expert Comment

by:_b_h
ID: 16959170
Hi, Harshu

If a data queue is attached to an output queue, an entry is sent to it when a spooled file has a status of ready. These entries can be received by a VB program that checks the data queue.
To create a data queue named MYDTAQ in library MYLIB:
 CRTDTAQ    DTAQ(MYLIB/MYDTAQ) MAXLEN(128)
To attach the data queue to the output queue MYOUTQ in MYLIB:
CHGOUTQ MYLIB/MYOUTQ DTAQ(MYLIB/MYDTAQ)

A sample VB program that checks a data queue can be found in another question:
http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_20525743.html

Your VB program can then issue the CPYSPLF command to copy the report to a physical file. Then the physical file can be downloaded by the VB program.

If you have questions, please post back!
Barry
0
 

Author Comment

by:harshbala
ID: 16960317
Hi,
I saw the 20714735 and 20525743. They are eye openers but still to do in RPG / CL I will have to know from steps A to Z.  Now this MPRINT outq get updated automatically from the software the company uses. Can i make a new dataQ as HmyDataQ and attach it to the existing MPRINT and PRT03 outqueues. If so, then it implies that when ever a spoolfiles hit MPRINT an entry will automatically be written to my dataQ (HmyDataQ). Still the question remains as how to pick all of them and convert to pc files and store on the PC automatically.
I am getting the idea but as i am not well versed with as400 it looks like a mirage, if you know what i mean.
Regards,
harshu.





0
 
LVL 14

Expert Comment

by:daveslater
ID: 16960916
Hi
what do you mean
"Convert to PC Files". There is some freewear that will convert a standard AS/400 print to PDF, this could then be copied to the QDLS.
If money is not a problem the IBM Infoprint software will do this all automatically.

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16963252
harshu:

Possibility #1: iSeries Access APIs to handle spooled files. See:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaik/rzaikcaexpapiover.htm

...for an introduction in the V5R3 InfoCenter. Look over the SOA APIs in particular.

Possibility #2: Define an iSeries Access printer session and have your output printed through it. Have it output to a file instead of a printer. Once there, handle it any way you choose.

Possibility #3: Remote outq to a Windows spooler print queue. And define it on the Windows side so it outputs to a file instead of a printer. LPR/LPD could be implemented in a variety of ways.

Of course, once it's in the Windows spooler, there are lots of Windows ways to manipulate it, right?

Lots of ways to get your request done without actually needing RPG or CL programming, though some would require running basic configuration commands to set up.

Tom
0
 

Author Comment

by:harshbala
ID: 16978161
We have V5R2 and  not V5R3. Secondly as i m a programmer, they donot want to pay for any third party software. I did not understand possibility #2 and also #3. how to do it. Can you be more elaborate tom.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16987681
harshu:

1. For V5R2, it's pretty much the same. It doesn't matter a lot, but there are some differences. Follow the navigation in the V5R2 InfoCenter to find the similar page at:

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzaik/rzaikapiinterfaces.htm

No 3rd-party is involved. You'd write code to call the APIs on a PC. You would install the iSeries Access components on the PC so the APIs could be called.

2. iSeries Access supplies terminal and printer emulation although the emulation component is licensed. You can create a printer session on a PC and your AS/400 can print to it just like it was an actual printer. Windows allows you to send printer output to a file on the PC instead of to a physical printer. That's normal printer setup stuff under Windows. I try to always have a Windows printer defined that routes to a text file just in case I want one in a hurry. With an iSeries Access printer session in the middle, it gives me a real quick way to send any spooled file on my AS/400s to a Windows *.TXT file.

3. OS/400 can route spooled files to other systems through mostly standard LPR/LPD. This is often done by creating an output queue on the AS/400 that has a remote IP address as an attribute. Use the CRTOUTQ command and review the help text, note the RMTSYS() parameter help in particular. If routed to a Windows PC, you can set up the printer queue to output to a file much like above.

In Windows XP, select to add a printer. (Other Windows versions have similar options.) Select 'Local printer', then instead of 'LPT1:', select 'FILE: (print to file)' for the port. That gets you going on connecting Windows printed output to a file. From there, it's all Windows programming, not AS/400.

Those three alternatives were listed in order to give you possibilities that need no programming on your AS/400. If you aren't an AS/400 programmer, you might feel more comfortable doing programming on the PC. However, I'm not a real proficient PC programmer, so I'm not a good source of Windows 'expert' advice. Just some possibilities.

Tom
0
 

Author Comment

by:harshbala
ID: 17022728
Sorry, didnot repond as was sick. I will try it out tom and update.
Regards,
Harsh
0
 
LVL 17

Expert Comment

by:Dushan911
ID: 17029025
Simply you can access DB2 with SQL commands using DB2 Database Driver(like ODBC). Its comming with Crystal Reports also.
You can use SQL with RPGLE via SQLLE. Don't worry thats same as SQL.
Or you can use VB, Java, PHP, ASP ..etc with DB2 with SQL commands.

You can write SQL SP in AS400 server using RPGLE with SQLLE. You can have idea from following solutions.

http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_21791137.html
http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_21787564.html
http://www.experts-exchange.com/Programming/Programming_Platforms/AS400/Q_21797864.html

BR Dushan
0
 

Author Comment

by:harshbala
ID: 17040444
Sorry,
May be i m not too good at either windows or anyother operating system.  If anybody can give a booster dose by giving me step by step instructions and the code to be in vb6. If anybody can help me I would be ggggggrrrrrrrreeeeeeaaaaaattttttt ful. Sorry to be so dumb. As I am brand new to As400 I am stumped. Now, I am thinking why did I take up this new job when i was not prepared. Also if anybody can suggest a good book for RPG programming, I would at leaset start on the first step. Thanks & regards,
HarshBala.
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17040869
Hi harshbala
the programming approach that you are trying to adobt is quite technical but first some clarification.

1) I want to sort my MPRINT and the PRT03 outqueue
is this to be sorted and viewed on the AS/400 or on a PC

2) I want to be able to access these output queues and pickup the spoolfiles and store into sql server database for future reference and also to be able to print the reports using Sql server database.
Do you want to store the print as a "PC print file" or in a format that can be copied back to the AS/400 for printing?

Regards

Dave
0
 

Author Comment

by:harshbala
ID: 17048580
Hi Dave,
1) Yes I want to be able to sort my mprint queue and the prt03 queue and view in the green screen, so that it is easier to locate various spoolfiles. This list of spoolfiles is huge and it becomes a difficult task to select the reports and send for printing as i donot know how to group them so that it is easier for me to send various batches.

2) I want to store these files on to the PC by converting them to MSWord or Crystal reports 8.5 or 9 Which ever is easier but using vb6 as i donot know ABC of RPG programming. I want to give more meaningful name as say GL20060702 which i can store in sql server 2000 database as a referance for future use, so that i would be able to pick these files up at later stage from vb program and show the users these reports on screen or print it even on PC printer and not on AS400 connected printer.

Basically I want to export these reports from AS400 to PC and store it there  (Archive it using sql server database). It would be great if I am able to achieve a) storing the reports as it with its original formatting intact as i mentioned above by giving it meaning ful names for future referance    b) storing only the data in sql server database for each report so that it can be exported to Excel etc using the data from sql server database.

Dave, I have a feeling that u will be able to help me. If the above is not possible with vb6 then I would require step by step instructions of how to write in rpg and how to compile etc etc etc etc.........

Regards,
Harsh Bala



0
 

Author Comment

by:harshbala
ID: 17082352
Hi Dave,
Any luck on how to do it. I am still searching. Hope somebody knows how to solve my problem. As I am pressed for the time I can increase the points to say 500.


Regards,
Harsh Bala.
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17094146
Hi
the program is complicated, it is using API's, User spaces and subfiles and still needs work to be completed - You need to decide howyou want the spool file to be transferred.

Dave
0
 

Author Comment

by:harshbala
ID: 17097087
hi Dave
Sorry got little bogged down.
1) I would prefer to transfer the spool files using vb6 as i know vb6 than rpgl. This MPRINT outqueue is the outqueue  which gets all the spoolfiles after the end of day is run. So I need to transfer all these spool files to PC environment. Another way is to transfer all the spool files to PC irrespective of whom is it to be distributed. Once it goes to PC environment and I am able to view it using (either word or crystal reports), I can distribute these reports in windows environment (which is not a problem).

2) Next step is to store it in sql server datafiles so that any query can be run to print older reports at a future date.

But the 1st option is really urgent as this is the main requirement. I have got used to basic commands of AS400 like wrkoutq etc etc. but still do not know enough to be in able to write rpgl etc. Sorry to press you for time, but my situation is like this now.

And thanks for reply. I thought my question has been forgotten.

regards,
Harsh Bala.
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17097876
Hi
what sort / grouping criteria do you want?

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17100087
hi
I have had to do it a quick and not very efficient way.
each post will be some source that needs compiling.

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17100100
Name: SRTOUTQP
Type: PF
SRCF: QDDSSRC
Text: Workfile for SRTOUTQ
----------------------------------------------------------------
.....A..........T.Name++++++RLen++TDpB......Functions+++
*************** Beginning of data **********************
     A          R SRTOUTQR                              
     A            SROUTQ        10                      
     A            SRFILE        10                      
     A            SRUSER        10                      
     A            SRJOB         10                      
     A            SRJNBR         6                      
     A            SRSNBR         5  0                  
     A            SRUDTA        10                      
     A            SRSTS          5                      
     A            SRPAGE         5  0                  
     A            SRCOPY         5  0                  
     A            SRFORM        10                      
     A            SRPTY          1                      
     A            SRDATE          L                    
     A            SRTIME          T                    
****************** End of data *************************
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17100111
Name: SRTOUTQD
Type: DSPF
SRCF: QDDSSRC
Text: List Output queue details
----------------------------------------------------------------
.....AAN01N02N03T.Name++++++RLen++TDpBLinPosFunctions+++++++++++++++++++++++++++
*************** Beginning of data ***********************************************
     A                                      DSPSIZ(24 80 *DS3)                  
     A                                      CA03                                
      ********************************************************************      
     A          R @@HDR                                                          
     A                                  1 20'Spool file selection'              
     A                                  1 73DATE                                
     A                                      EDTCDE(Y)                            
     A                                  2 73TIME                                
      ********************************************************************      
     A          R @@SFL                     SFL                                  
     A            SFSEL          2A  B  5  2                                    
     A            SFOUTQ    R        O  5  6REFFLD(SROUTQ *LIBL/SRTOUTQP)        
     A            SFUSER    R        O  5 17REFFLD(SRUSER *LIBL/SRTOUTQP)        
     A            SFJOB     R        O  5 28REFFLD(SRJOB *LIBL/SRTOUTQP)        
     A            SFUDTA    R        O  5 39REFFLD(SRUDTA *LIBL/SRTOUTQP)        
     A            SFSTS     R        O  5 50REFFLD(SRSTS *LIBL/SRTOUTQP)        
     A            SFFORM    R        O  5 56REFFLD(SRFORM *LIBL/SRTOUTQP)        
     A            SFDATE         8   O  5 67                                    
     A            SFFILE    R        O  6  6REFFLD(SRFILE *LIBL/SRTOUTQP)        
     A                                      COLOR(BLU)                    
     A            SFSNBR    R        O  6 17REFFLD(SRSNBR *LIBL/SRTOUTQP)  
     A                                      COLOR(BLU) EDTCDE(Z)          
     A            SFJNBR    R        O  6 28REFFLD(SRJNBR *LIBL/SRTOUTQP)  
     A                                      COLOR(BLU)                    
     A            SFPAGE    R        O  6 44REFFLD(SRPAGE *LIBL/SRTOUTQP)  
     A                                      COLOR(BLU) EDTCDE(Z)          
     A            SFPTY     R        O  6 54REFFLD(SRPTY *LIBL/SRTOUTQP)  
     A                                      COLOR(BLU)                    
     A            SFCOPY    R        O  6 61REFFLD(SRCOPY *LIBL/SRTOUTQP)  
     A                                      COLOR(BLU) EDTCDE(Z)          
     A            SFTIME         8   O  6 67                              
     A                                      COLOR(BLU)                    
      ********************************************************************
     A          R @@SFLCTL                  SFLCTL(@@SFL)                  
     A                                      OVERLAY                        
     A  31                                  SFLDSP                        
     A                                      SFLDSPCTL                      
     A N31                                  SFLCLR                        
     A  31                                  SFLEND(*MORE)                  
     A                                      SFLSIZ(0009)                          
     A                                      SFLPAG(0008)                          
     A            RRN1           4  0H      SFLRCDNBR                            
     A                                  3  6'Outq       User       Job        U-  
     A                                      ser data  Stat  Form Type  Date'      
     A                                  4  2'Sel Spl File   Splf Nbr   Job Nbr -  
     A                                              Pages Pty   Copies     Tim -  
     A                                          '                                
     A                                      DSPATR(UL)                            
      ********************************************************************        
     A          R @@FTR                     OVERLAY                              
     A                                 22  2'                                  -  
     A                                                                         -  
     A                                               '                            
     A                                      DSPATR(UL)                            
     A                                 23  2'F3=Exit'                            
****************** End of data ***************************************************
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17100120
Name: SRTOUTQC0
Type: CLLE
SRCF: QCLSRC
Text: Inital program for SRTOUTQ
 ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ..
*************** Beginning of data ***************************************
             PGM                                                        
                                                                         
             DLTF       FILE(QTEMP/SRTOUTQP)                            
             MONMSG     MSGID(CPF0000)                                  
                                                                         
             CRTDUPOBJ  OBJ(SRTOUTQP) FROMLIB(*LIBL) OBJTYPE(*FILE) +    
                          TOLIB(QTEMP)                                  
                                                                         
             CALL SRTOUTQ                                                
                                                                         
             ENDPGM                                                      
****************** End of data ******************************************


0
 
LVL 14

Expert Comment

by:daveslater
ID: 17100127
Name: SRTOUTQC1
Type: CLLE
SRCF: QCLSRC
Text: Convert Outq to file

 ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+.
*************** Beginning of data ******************************************
             PGM        PARM(&OUTQ)                                        
                                                                           
             DCL        VAR(&OUTQ) TYPE(*CHAR) LEN(10)                      
                                                                           
             CRTPF      FILE(QTEMP/SPLF) RCDLEN(132)                        
             MONMSG     MSGID(CPF0000)                                      
                                                                           
             OVRPRTF    FILE(QPRTSPLQ) HOLD(*YES)                          
                                                                           
             WRKOUTQ    OUTQ(&OUTQ) OUTPUT(*PRINT)                          
             CPYSPLF    FILE(QPRTSPLQ) TOFILE(QTEMP/SPLF) SPLNBR(*LAST)    
             DLTSPLF    FILE(QPRTSPLQ) SPLNBR(*LAST)                        
                                                                           
             DLTOVR     FILE(QPRTSPLQ)                                      
                                                                           
             ENDPGM                                                        
****************** End of data *********************************************
                                                                           
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17100197
Name: SRTOUT
Type: SQLRPGLE
SRCF: QRPGLESRC
Text: show sorted outputq

     ‚****************************************************************************
     H CopyRight('DaveSlater')
     H DftActGrp(*No)
     H ActGrp(*Caller)
     H Option(*NODEBUGIO)
     ‚****************************************************************************
     FSrtOutqD  Cf   e             Workstn Sfile(@@SFL:RRN1) INFDS(INFODS)
     FSPLF      IF   F  132        DISK    USROPN  EXTFILE('QTEMP/SPLF')
     FSrtOutqP  if a e             disk    UsrOpn  EXTFILE('QTEMP/SRTOUTQP')
     ‚****************************************************************************
     ‚** cl to create outq print and copy to file SPLF
     DSRTOUTQC1        Pr                  EXTPGM('SRTOUTQC1')
     D OUTQ                          10    Const
     ‚****************************************************************************
     ‚** procedure to read SPLF and forat output to SrtOytQp
     DPopulateWorkFile...
     D                 Pr
     D OUTQ                          10    Const
     ‚****************************************************************************
     ‚** Local procedures
     DLoadSubFile      Pr
     DOpenSql          Pr
     DReadSQL          Pr
     DCloseSql         Pr
     DCallPgm          Pr
     ‚********************************************************************
     ‚** current subfile page on enter
     D INFODS          DS
     D  SFLPAG               378    379B 0
     ‚****************************************************************************
     ‚** Definition for SPLF fixed length file
     DSrtOutqDs        DS           132
     D  WKFILE                       10    OVERLAY(SrtOutqDs :  2)
     D  WKUSER                       10    OVERLAY(SrtOutqDs : 13)
     D  WKJOB                        10    OVERLAY(SrtOutqDs : 84)
     D  WKJNBR                        6    OVERLAY(SrtOutqDs : 95)
     D  WKSNBR                        5s 0 OVERLAY(SrtOutqDs : 74)
     D  WKUDTA                       10    OVERLAY(SrtOutqDs : 24)
     D  WKSTS                         5    OVERLAY(SrtOutqDs : 36)
     D  WKPAGE                        5s 0 OVERLAY(SrtOutqDs : 42)
     D  WKCOPY                        5s 0 OVERLAY(SrtOutqDs : 48)
     D  WKFORM                       10    OVERLAY(SrtOutqDs : 55)
     D  WKPTY                         1    OVERLAY(SrtOutqDs : 67)
     D  WKDATE                        8    OVERLAY(SrtOutqDs :102)
     D  WKTIME                        8    OVERLAY(SrtOutqDs :111)
     D  WkHPage                       4    OVERLAY(SrtOutqDs :119)
     ‚****************************************************************************
     ‚** Workfields
     D LoopToFree      s               n   Inz(*On)
     D SortFields      s             64    Inz('SROUTQ, SRDATE , SRUDTA')
     D RRn1f           s             10i 0
     D Pgm             s             10
     ‚****************************************************************************
      /Free
        *inlr = *on ;  //‚Always exit with lr on

        Open  SrtOutqP ; //‚open workfile

        SRTOUTQC1('DQPRINT') ;   //‚dupm first outputq to fixed file
        PopulateWorkFile('DQPRINT') ;  //‚format first outputq

        SRTOUTQC1('DPDOUTQ') ;   //‚dupm second outputq to fixed file
        PopulateWorkFile('DPDOUTQ') ; //‚and process .. do as many times as needed

        close SrtOutqP ; //‚close workfile, we will now use SQL to process

        LoadSubFile();  //‚load the subfile will all outq details - sorted

        *in31 = rrn1 > 1 ;  //‚check if anything to display
        rrn1f = 1 ;  //‚set first page

        Dow LooptoFree ;

           rrn1  = rrn1f ;  //‚set current page
           write @@hdr ;   //‚header
           write @@Ftr ;   //‚footer
           Exfmt @@SFLCTL ;  //‚outq details
           if *inkc or Not *in31 ;  //‚exit of F3 or no records
              leave ;
           endif ;
           rrn1f = sflpag ;  //‚save current page

           Dow LooptoFree ;  //‚process changed records
              readc @@sfl ;
              if %eof  ;
                 leave ;
              endif ;

              if Sfsel = ' ' ;  //‚ignore balnks
                 Iter  ;
              endif ;

              pgm = 'SRTOUTQO' + %trim(SfSel) ; //‚calculate program name
              clear Sfsel ;  // clera and update selection
              Update @@sfl ;
              callpgm() ;

           enddo ;

        enddo ;

        Return ;
      /End-Free
     ‚******************************************************************************************
     PPopulateWorkFile...
     P                 B
     dPopulateWorkFile...
     d                 pi
     D OUTQ                          10    Const

      /Free
        open splf ;  // open list of output

        Dow LooptoFree;
           Read splf SrtOutqDs ; // read into formatted buffer
           if %eof ;  // exit on eof
              leave ;
           endif ;

           // ignore non-data records
           if wkHPage = 'Page' or wkTime = 'Time' or wkTime = *Blank ;
              Iter ;
           endif ;

           // move to workfile
           SrOutq = Outq    ;
           SrFILE = WKFILE  ;
           SrUSER = WKUSER  ;
           SrJOB  = WKJOB   ;
           SrJNBR = WKJNBR  ;
           SrSNBR = WKSNBR  ;
           SrUDTA = WKUDTA  ;
           SrSTS  = WKSTS   ;
           SrPAGE = WKPAGE  ;
           SrCOPY = WKCOPY  ;
           SrFORM = WKFORM  ;
           SrPTY  = WKPTY   ;
           SrDATE = %Date(WKDATE : *DMY/)  ;  // ** Note date & time format
           SrTIME = %Time(WKTIME : *HMS:) ;   // ** may need changing

           write SrtOutqR ;  // output to workfile

        enddo ;
        close splf ;  // close workfile
      /End-Free
     PPopulateWorkFile...
     P                 e
     ‚******************************************************************************************
     PLoadSubFile      B
      /Free
        //‚Initialsie subfile
        clear *in31 ;
        clear @@sfl;
        clear @@sflCtl ;
        Write @@sflCtl ;

        //‚Open file in correct sort order
        OpenSql();

        //‚Process sorder file
        dow LoopToFree;
           ReadSQL();
           if sqlcod <> 0 ;
              leave ;
           endif ;

        // move from workfile into subfile
           SfOUTQ = SROUTQ ;
           SfFILE = SRFILE ;
           SfUSER = SRUSER ;
           SfJOB  = SRJOB  ;
           SfJNBR = SRJNBR ;
           SfSNBR = SRSNBR ;
           SfUDTA = SRUDTA ;
           SfSTS  = SRSTS  ;
           SfPAGE = SRPAGE ;
           SfCOPY = SRCOPY ;
           SfFORM = SRFORM ;
           SfPTY  = SRPTY  ;
           SfDATE = %char(SRDATE : *dmy )  ; // ** Note date & time format
           SfTIME = %char(SRTIME : *hms )  ; // ** may need changing

           rrn1 = rrn1 + 1 ; // output to subfile
           write @@sfl ;

        enddo ;

        CloseSQL();

      /End-Free
     PLoadSubFile      E
     ‚******************************************************************************************
     POpenSql          B
     D SQL             s           1024    Varying

      ** select all records from workfile in the order required
      /Free
        SQL = 'select * from QTEMP/SRTOUTQP order by ' + SortFields ;
      /End-Free

      ** Dynamic SQL generation
     C/Exec SQL
     C+ PREPARE SRTOUTQP FROM :Sql
     C/End-Exec
     C
     C/Exec SQL
     C+ DECLARE SRTOUTQC CURSOR FOR SRTOUTQP
     C/End-Exec
     C
      ** Open queried file
     C/Exec SQL
     C+ OPEN SRTOUTQC
     C/End-Exec
     C
     POpenSql          E
     ‚******************************************************************************************
     PReadSql          B

     C/Exec SQL
     C+ FETCH FROM SRTOUTQC INTO
     C+ :SROUTQ, :SRFILE, :SRUSER, :SRJOB, :SRJNBR, :SRSNBR, :SRUDTA ,
     C+ :SRSTS, :SRPAGE, :SRCOPY, :SRFORM, :SRPTY, :SRDATE, :SRTIME
     C/End-Exec

     PReadSql          E
     ‚******************************************************************************************
     PCloseSQl         B

     C/Exec SQL
     C+ Close SRTOUTQC
     C/End-Exec

     PCloseSql         E
     ‚******************************************************************************************
     PCallPgm          B

      ** call processing program - your responsability to code it.
     C                   call(e)   Pgm
     C                   Parm                    SfJNBR
     C                   Parm                    SfUser
     C                   Parm                    SfJob
     C                   Parm                    SfFILE
     C                   Parm                    SFSNBR

     PCallPgm          E
0
 

Author Comment

by:harshbala
ID: 17116693
Oh my god,
What is it dave ?????????


The grouping of the various spool files not required. Sorting alphabetical would be fine.

Donot know how to react to it.

HarshBala
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17121251
Hi
these are the programs to list the outputq, show them on a screen, sorted, and allowing a selection.

to enter the code use PDM.
from a command line type
STRPDM
and press enter, then take option 3.

Source file is  QRPGLESRC or QCLSRC or QDDSSRC I have indicated this on each of the sections.

F6 allowes you to create a new source
Name: SRTOUT - this is the member name
Type: SQLRPGLE -  the spource type

14 compiles the source into a specified library

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17125410
HarshBala:

One question to help us understand your position...

If you had files in the Windows spooler, do you know how to write programs to list them and control where they print? If you know how to do it in Windows, we can supply info to show you how to translate that coding to the AS/400 spooler queues.

Tom
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:harshbala
ID: 17381282
Sorry,
Had to go on medical leave. Had a nose operation and took some time to recover. Just joined back. tliotta
I know the windows spool file as spoolsv.exe, but i donot know how to control it in windows. But back to my old question.
Dave,
I have printed out the codes you have posted here. Dave are you still around.

Regards,
Harsh Bala.

0
 
LVL 14

Expert Comment

by:daveslater
ID: 17381514
Hi
still here.
"spoolsv.exe," - you might need to post a question in the windows section
Dave
0
 

Author Comment

by:harshbala
ID: 17398535
Hi,
Back to my old question ? Still have not done anything about printing the spoolfiles from as400 to pc files and archieve using vb6 . Dave can i just access the specific outq say MPRINT on as400 and just convert some specific spoolfiles to pc files automatically everyday.

regards,
Harsh Bala.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17406485
Harsh:

It can be done automatically, but you will need to define the term "pc files".

A spooled file must contain printer control characters. E.g., a spooled file doesn't contain 'blank lines'. Instead, each printed line contains control characters that tell the printer what to do after printing the line. A control character might tell the printer to skip one or more lines after printing; each 'skipped' line would result in a 'blank' line on the physical printer.

Or the control character might tell the printer to 'eject a page' or 'skip to new page'. This would result in any number of 'blank' lines on the rest of the current page (and those blank lines would _not_ appear in the spooled file).

In order to create a 'pc file', you need to describe how the 'eject page' would be repesented in the pc file. Would it be different if you printed to a paper page that was 11" long rather than a page that was 14" long? You _could_ embed printer characters in the pc file, but you must decide which printer language the control characters should represent.

iSeries Navigator contains a drag-and-drop function that can convert spooled files to pc text files automatically. Test that function to determine if the format of the resulting pc file is what you want. If it isn't then please supply a definition that meets your needs.

Tom
0
 

Author Comment

by:harshbala
ID: 17409611
Dear Tom,

I have already tried draging the spool file from the iseries navigator and dropped it on desk top. I want to convert the spool files to PC files with the format intact as it is originally in the spoolfile. I want all the page breakes as it is but preferably printed on 80 col instead of 132 as is the case now for all the spool files.  As I have mentioned before I want to convert most of the spoolfiles of a specific outq (MPRINT) to the pc files  and then copy them to a directory for archieving.
Hope I am clear about the requirement and hope somebody can help me.

regards,
Harsh Bala.


0
 
LVL 27

Expert Comment

by:tliotta
ID: 17414016
Harsh:

The problem is with "format intact". Intact for what?

Do you want the print-lines converted to ASCII? If so, do you want printer control characters converted to ASCII? Are you expecting to print on a PC printer? or would it always be uploaded back to the AS/400 for printing?

Note that the CPYSPLF command can take a simple spooled file from an *OUTQ and place the content into a flat file. This file can also hold printer control characters. Once in a flat file, you can download it, archive it, upload it later and convert back to a spooled file. While it's in a pc file, it will be viewable though printer spacing won't be visible.

Try this on your AS/400:

1. Create some throwaway spooled file to play with. Doing something like pressing the host-print-screen key on an emulator session can created a spooled file in your current job. With iSeries Access, the spooled file name will be QSYSPRT.

2. Run these commands:

  ==>  crtpf  QTEMP/tstspl  rcdlen( 133 )
  ==>  cpysplf  file( qsysprt ) tofile( QTEMP/tstspl ) ctlchar( *FCFC )

3. That would take the only spooled file named QSYSPRT in your current job and copy its content into a temporary flat file named TSTSPL. The flat file was created as 133 characters long in order to hold 132-char print lines plus a 1-char forms-control character. If you created the flat file in a permanent library, it would be available for download. If you wished, you could run a CPYTOSTMF command to copy it out to a streamfile in the IFS. If the IFS directory was shared, you could grab the streamfile from a PC.

4.  Now run:

  ==>  ovrprtf  file( QSYSPRT ) ctlchar( *FCFC )
  ==>  cpyf  fromfile( QTEMP/tstspl ) tofile( QSYSPRT )
  ==>  dltovr  QSYSPRT

That last series should take the content of your temporary flat file and convert it back into a spooled file. Your job should now have two spooled files named QSYSPRT. They should look the same.

That should demonstrate how a spooled file _might_ be placed inside of a flat file and how a flat file can be converted to a spooled file. The [*FCFC] told the system how to handle printer forms characters during the conversions back and forth.

If you play with those commands, you'll see what's happening. From there, what you need is a way to programmatically access an *OUTQ to find spooled files and a way to upload/download.

Does that make sense? If it does, we can cover some details that can complicate the whole thing.

Tom
0
 

Author Comment

by:harshbala
ID: 17417646
Dear Tom,

Thanks, and i have already tried the 1st series of commands which would convert a spool file to the pcfile as shown below. i copied your commands.

  ==>  crtpf  QTEMP/tstspl  rcdlen( 133 )
  ==>  cpysplf  file( qsysprt ) tofile( QTEMP/tstspl ) ctlchar( *FCFC )

The resultant PC file as 0 or 1 in the first column, which i believe is for spacing or page throw. But if u notice when i print on a pc printer the resultant pc file the blank lines and page throw are not there, which i want, so when it is printed it has the format intact(page throw and blank lines).

And you have got it right. I want to search and find the specific spoolfiles only in one outq (MPRINT) where all the spoolfiles gets generated during the day. So I want to write a program where i will (specify the name of the spool files or all spool files)  that depends on me to specify and see if the specified spoolfiles are available in MPRINT outq and if yes then convert them to a pc files and store it on a PC. If a specific spool file is not available i want to go forward and check the next one and so on.

regards,
Harsh Bala








About the second series of commands i have not tried. i will try it.




0
 

Author Comment

by:harshbala
ID: 17454223
hi,
is anybody there

cheers,
harsh bala
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17454443
Hi
i have been thinking about this. There is no one solution to the problem. As Tom has said the PC format is dependant on the printer attached to the PC and the as/400 format, once converted, is lost.

There are other obsticals:.
The as/400 has different print formats SCS, IPDS, AFP, AFPDS, USERASCII. dependant on how the print was ceated is dependant on ho it is printed.

The only safe way to do what you want is to store two objects.
1) PC output for what you want in as yet an undetermined format.
2) A user space object with the original AS/400 spool data saved in it. This user space would then need to be put in a savefile and stored on the server via say FTP. This would then need to be associated with the PC version of the file.

Tom
what is your take on this?

dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17458177
There is a very basic standard ASCII printer format, though I don't know precise details. AFAIK, it's nothing more than inserting lines that contain standard ASCII characters such as CR and LF at the right points. The line-control characters can be at end-of-line. Obviously all printline text must also be converted to ASCII. I don't recall what ASCII character results in FormFeed, but I could look it up in an ASCII table if necessary.

If pure ASCII generated from a straight SCS spooled file is _ALL_ that is required, it shouldn't a major conversion. And if only one *outq is to be handled, that should be fairly straightforward too.

It just needs to be very clear what the restrictions are. No fancy printer resources, no bold/underline/etc., no images... That's the way it goes unless someone wants to write a convertor from one printer linguage to another. (Not a bad contract to take on, but definitely not a simple task no matter platform is involved.)

Tom
0
 

Author Comment

by:harshbala
ID: 17461013
Hi Dave, tom,

The as/400 has different print formats SCS, IPDS, AFP, AFPDS, USERASCII. dependant on how the print was ceated is dependant on how it is printed.

The outputs printed here does not have any image, nor any underline or bold characters. These reports gets pritned on an IBM dot matrix printer. The basic formating required is Page throw, blank lines in between or at the end of the page and the standard columns across the page. One more thing all these reports when printed on IBM printer are 132 column reports, but if these same reports are printed on HP laserjet printers, they are printed on A4 size paper in landscape orientation automatically. we just push these reports to HP laserjet printer queue.

In other words I require very simple formating to be intact (blank lines, page throw), Only one outputQ to search for called MPRINT (all the reports generated are in MPRINT outq only). Only I would like to specify the spoolfiles by its name or i may want to convert all the spoolfiles which are there (i need to be able to search the outq with the spoolfile i tell it to search)

Hope it is more clear. I donot know how to explain in a better way. If it is possible i can scan a page and upload somewhere to see the output.

Sorry to be such a pain but not being an expert in the AS400 or CL prog or RPGL i have no other choice but to rely on you experts.

reagards,
Harsh Bala.

0
 
LVL 14

Expert Comment

by:daveslater
ID: 17463005
Hi
I have created a windows printer that is "Generic / text" and outputs to a file.
Then created a client access printer that performs no conversion.
When this process an as/400 file it creates the standard text file with page brakes in the correct place.

I have posted a question in the VB area to see if the naming of the print can be automated.

we can use this file on the pc to print via netsever to create a new as/400 file so theoretically we have the basis of a solution.

I'll leave the vb qursion open for a week or so to get some feedback.

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17469027
Hi
I have an answer back for the VB see

Http:Q_21979667.html#17467705

Now create a printer session using client access.
 use the option to "Transform print data to ASCII on the iSeries"
for the Printer Model use *NONE

and a generic text printer to windows and you have all the building block to create the output in PC format.

Do you need more detail?

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17475509
Dave:

Superb example of using EE to transfer knowledge. The redirected use of the VB forum is worth the points even if no other value was received.

Side question...

Harsh:

>  Sorry to be such a pain but not being an expert in the AS400 or CL prog or RPGL i have no other choice but to rely on you experts

That's a tough position to be in no matter what the platform is. What languages are you familiar with? Does your site have OS/400 development experience at all?

I often give answers that use CL as the language because _every_ AS/400 has CL available. I sometimes comment in RPG because the thread asks for it. But various other languages are possible. COBOL? C? Java? Other (some are supported at times)?

Tom
0
 

Author Comment

by:harshbala
ID: 17492163
Hi Dave/Tom

Your solution looks wonderful. Let me do it and see. I will revert back. About Tom's Q. I know MS MF Cobol, Dbase, VB5, VB6 only. But at my site there is nobody who knows any programming in CL or RPG and my administrator has informed me that we donot have the Cobol compiler license. So I have only VB6 option left with me.

regards,
Harshu
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17492478
Harshu
I have been doing some playing over the week-end.
Have a play and see if if basics of the solution works. If so then I have a idea the will allow you do do almost everything in VB (with a few one CL program).

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17496695
Harsh:

Do you know what version of OS/400 your system runs? If you have at least version V5R1 and a license for (1) RPG and (2) development tools such as SEU and PDM, then you automatically have a license for COBOL and C and a few other things. It appears that you have RPG. In order to maintain any significant program code, a development tools license is almost required, _especially_ for a shop that has very limited experienced staff.

Note that there is a difference between "no license" and "not installed". It's possible that other languages simply weren't installed on a system that might have upgraded from the previous version 4.

Tom
0
 

Author Comment

by:harshbala
ID: 17500274
Dear Tom,

We have V5R2 version. But about the licenses I have no idea. I know very few basic commands like working with outqueues, or wrkactivejob or something like that. RPG is there I suppose. But I donot know even basic of RPG where to write it, how or where does it gets stored, how to compile it and how toexecute it etc etc. and so I am scared to do any RnD on the server as that is the live server. If anything wrong happens because of me, I will ofcourse loose my job and donot know what else would happen and even our administrator is a novic with no prorgamming knowledge, and so he does not allow me to try anything which i am not sure of. So if you give me any solution in RPG etc you will also have to give instructions like you would give to a child. I have given the answer that dave gave me to my admin and am waiting for him to try 'cause he said he will try it later and tell. I will wait for him for 1-2 days or else please tell me to How to ?
create a printer session using client access.
 use the option to "Transform print data to ASCII on the iSeries"
for the Printer Model use *NONE

I have already created the generic text printer driver on windows.
Another thing how do we find out which print formats are we printing in. Our output gets printed to IBM 4247. Different print formats SCS, IPDS, AFP, AFPDS, USERASCII.

regards,
Harshu.

0
 
LVL 14

Expert Comment

by:daveslater
ID: 17500545
first do you have client access installed on a PC?
You can contact me at my EE email address - see my profile. I can then e-mail some screen shots, this will be a lot easier.

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 17505713
Harshu:

Dave is good at guiding new people through the unfamiliar. Now that your situation is clear to us, he can provide as needed. I'll watch the thread and drop in any extra pieces if it seems useful, but Dave can own it.

It can be very uncomfortable to have responsibilities on an unfamiliar system no matter what platform it is. I don't envy you at the moment.

Tom
0
 

Author Comment

by:harshbala
ID: 17509538
Dear Dave/Tom

Thanks, Yes I have the client access installed. I have sent a mail to dave.

regards,
Harsh Bala.

0
 
LVL 14

Accepted Solution

by:
daveslater earned 500 total points
ID: 17510257
Harsh
Documentation sent.
one other point. The AS/400, iSeries, i5 what ever you want to call it is one of the most stable compuetr systems ever developed. It will run 24/7/365.
It is virtually impossible to crash - anything we post here is will not cause you AS/400 to crash - the most you will get is a message on the qsysopr message queue.
It is a true multi-tasking computer where all jobs are independant; what happens in one job will  not affect the rest of the system.

Dave
0
 

Author Comment

by:harshbala
ID: 17537386
Hi Dave,
Got your mail. It was really helpful and i did as you had told. The output comes out in text form. The data is ok. The original output is printed in 132 column printer. So this text when printed on A4 size paper landscape mode, the page throw is going beyond the page. I think it is really keeping the original format intact !!!!. Rest is wonderful. Thanks. I will find a way to print it in condensed mode. Is there a way to make it come on A4 landscape. I had used courier 10.
If I use courier 9 etc. the width is ok but the length of the page does not shrink. Also there is a control character printed before the new page on the left side.

Regards,
Harsh Bala
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17541723
On the
File / Printer Set-up menu select the “Set-Up” option. Try the landscape option.

Failing that, what you will get is the basic text with Chr(12) for a page break, it would be very simply to write a VB program to process the file and create a nicely formatted report
0
 

Author Comment

by:harshbala
ID: 17549580
Dear Dave,
I have already tried landscape and also to change the font to make it smaller, but the page throw seems to be beyond 1 page. I made it as letter -> landscape with small fonts. The width gets smaller but the length of the page does not seem to reduce. I think it has to do with the control characters that are there, that the page throw is not recognised. let me see if i go through each line -> character by character and get chr(12) and then see what to do.
Thanks once again for making my problem look so simple after all.

regards,
HarshBala.

0
 

Author Comment

by:harshbala
ID: 17589940
Hi Dave / Tom,
Many Many thanks for your wonderful tips.

regards,
HarshBala
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now