CL opnqryf question

2505 pts.
Tags:
AS400 Command
In my CL program, I have an Opnqryf and a Rcvf following that. It reads the file till EOF has reached. Now I have another opnqryf on the same file. I have closed the file just before the opnqryf but since the first loop has reaced eof, my second opnqryf is not retrieving any records. I dont want to take the number of records inhand as the data may change while am reading the file. And other than TfrCtl is there any better solution to achieve this?

Software/Hardware used:
AS400

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    The best choices require you not to use OPNQRYF at all; but you are using it anyway, so we already know that you don't want the best choices. Apparently you want worse choices. . So, one suggestion is to upgrade to i 6.1, and then declare two different files. If you're already running 6.1, try the CLOSE command. Or break your code into two separate blocks of code and make them two separate programs. . Beyond that, we'll probably need to see your programming to give suggestions. . Tom
    125,585 pointsBadges:
    report
  • BigKat
    you can use the POSDBF command to reposition to the start of the file
    8,210 pointsBadges:
    report
  • philpl1jb
    I would typically open the query file in one cl with override to share *Yes and then call a second program to process the records. With this logic you won't have a problem processing two queries.
    49,960 pointsBadges:
    report
  • TomLiotta
    Because EOF has been reached, POSDBF won't work. Without knowing the OS version and the programming, breaking it into sub-programs is about as much as can be said. -- Tom
    125,585 pointsBadges:
    report
  • RamvishakRamesh
    Tom,Could you please suggest any solution other than OPNQRYF? OS version is V5R4
    2,505 pointsBadges:
    report
  • TomLiotta
    To start, you should simply understand that CL is a "Control Language". It's not intended to be a database processing language. Use COBOL, or C, or RPG, or even REXX (or Java?), if you need to handle variable queries. Those languages are written with database access in mind, but CL isn't.
    .
    However, at V5R4, you can DCLF up to five files. Maybe that's all you need to do. Run OPNQRYF for two different FileIDs and see if it works. I never used OPNQRYF as late as V5R4, so I don't know what will happen. There is no CLOSE command until i 6.1, so the two file definitions will remain open until the program ends. Once EOF is reached on either file, that's where it will stay as long as the program runs. If it doesn't work, you will have to break your code into at least two separate programs. Maybe into three, one main and two sub-programs.
    .
    A second suggestion is to use SQL VIEWs instead of OPNQRYF data paths. You can create a VIEW essentially as quickly as you can generate an OPNQRYF. A VIEW can access more PFs than OPNQRYF can, and they're better understood than OPNQRYF is today. If you're going to use RCVF to read records, it's just as easy to access a VIEW. The database processing is essentially the same either way. That is, both ways have to create on open data path for the job to use.
    .
    A SQL VIEW statement can be constructed with fairly simple CHGVAR statements to set a variable WHERE clause. It can be processed with the Qshell db2 utility or in other ways. Once built, RCVF can be run over it. To use RCVF, you have to DCLF the file; and a version of the file with a matching record format must exist and be available in the job when the program is compiled.
    .
    If desired, CREATE VIEW can be processed directly in the CL by using the SQL CLI APIs. At V5R4, those APIs are easier to use than they are even in VB (ODBC) or through Java (JDBC). Here's an example of V5R4 CL executing a CREATE VIEW statement:
    /* +
       Basic example of CL executing a SQL statement. Structured for      +
       V5R4, but easily adapted to V5R3 by undoing subroutines. The       +
       use of CALLPRC with *BYVAL for parameter values restricts the use  +
       of pure CL prior to V5R3.                                          +
    */
                                                                      /* */
    pgm    ( +
           )
                                                                      /* */
       dcl   &SQL_RC     *int
       dcl   &hEnv       *int
       dcl   &hDBC       *int
       dcl   &hStm       *int
                                                                      /* */
       dcl   &subRC      *int           value( 0 )
                                                                      /* */
       dcl   &SQL_Stm    *char  256
                                                                      /* */
       dcl   &SQL_NTS    *int           value( -3 )
       dcl   &SQL_DROP   *int     2     value( 1 )
                                                                      /* */
       dcl   &smZero     *int     2     value( 0 )
       dcl   &x00        *char    1     value( x'00' )
                                                                      /* */
    /* Set up a SQL statement for an example...            */
                                                                      /* */
       chgvar      &SQL_Stm     ( +
                                 'CREATE VIEW QTEMP.myview01 AS +
                                  SELECT +
                                  CUSNUM, LSTNAM, INIT, BALDUE, +
                                  current date as CurDat +
                                  FROM qiws.qcustcdt +
                                  WHERE BALDUE > 100.00' +
                                  *cat &x00 +
                                )
                                                                      /* */
    /* Perform some initialization tasks...                */
                                                                      /* */
       callsubr InitSub      rtnval( &subRC )
                                                                      /* */
    /* Do a default connection for *LOCAL database...      */
                                                                      /* */
       callsubr GetConn      rtnval( &subRC )
                                                                      /* */
    /* Allocate a statement work handle and execute...     */
                                                                      /* */
       callsubr ExecStm      rtnval( &subRC )
                                                                      /* */
    /* Do seem cleanup tasks...                            */
                                                                      /* */
       callsubr FreeSub      rtnval( &subRC )
                                                                      /* */
       return
                                                                      /* */
    /* Subroutines are inserted between here... */
    /* */
    /* ... and here.                            */
    endpgm
    I separated subroutines into a second part, but that's just so you can see the above structure by itself. The four subroutines are just calls to the APIs. When they're put into blocks of code by themselves, they can be a little easier to see one at a time.
    /* ----------------------------------------------------------------- */
    /* Perform some initialization tasks...                */
                                                                      /* */
    subr     InitSub
                                                                      /* */
       callprc  'SQLAllocEnv'     ( +
                                    &hEnv        +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       callprc  'SQLAllocConnect' ( +
                                    ( &hEnv *byval ) +
                                    &hDBC        +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       rtnsubr  rtnval( 0 )
                                                                      /* */
       endsubr
                                                                      /* */
    /* ----------------------------------------------------------------- */
    /* Do a default connection for *LOCAL database...      */
                                                                      /* */
    subr     GetConn
                                                                      /* */
       callprc  'SQLConnect'      ( +
                                    ( &hDBC *byval ) +
          /* database (default) */  &x00         +
                                    ( &smZero *byval ) +
          /* user (default) */      &x00         +
                                    ( &smZero *byval ) +
          /* password (default) */  &x00         +
                                    ( &smZero *byval ) +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       rtnsubr  rtnval( 0 )
                                                                      /* */
       endsubr
                                                                      /* */
    /* ----------------------------------------------------------------- */
    /* Allocate a statement work handle and execute...     */
                                                                      /* */
    subr     ExecStm
                                                                      /* */
       callprc  'SQLAllocStmt'    ( +
                                    ( &hDBC *byval ) +
                                    &hStm        +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       callprc  'SQLExecDirect'   ( +
                                    ( &hStm *byval ) +
                                    &SQL_Stm     +
                                    ( &SQL_NTS *byval ) +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       callprc  'SQLFreeStmt'     ( +
                                    ( &hStm *byval ) +
                                    ( &SQL_DROP *byval ) +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       rtnsubr  rtnval( 0 )
                                                                      /* */
       endsubr
                                                                      /* */
    /* ----------------------------------------------------------------- */
    /* Do some cleanup tasks...                            */
                                                                      /* */
    subr     FreeSub
                                                                      /* */
       callprc  'SQLDisconnect'   ( +
                                    ( &hDBC *byval ) +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       callprc  'SQLFreeConnect'  ( +
                                    ( &hDBC *byval ) +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       callprc  'SQLFreeEnv'      ( +
                                    ( &hEnv *byval ) +
                                  ) +
                            rtnval( &SQL_RC )
                                                                      /* */
       rtnsubr  rtnval( 0 )
                                                                      /* */
       endsubr
                                                                      /* */
    /* ----------------------------------------------------------------- */
    I also uploaded the program code to PasteBin at http://pastebin.com/A3Srjmsr where it should be available for the next month in case this editor has trouble.
    .
    The program should compile fine at V5R4. I only tested on 6.1 though I did test a compile at the previous release. I put the example together after I saw your post last night. All the program does is create a view over the sample QCUSTCDT file from the QIWS library. The view is placed in QTEMP. You can compile and run the program, then look at the VIEW in QTEMP. Try RUNQRY over it to see a result. (Most systems have the QIWS library, but you can create a custom VIEW over any of your database files. Just change the &SQL_Stm variable value.)
    .
    I don't check the &SQL_RC return code, so that test should be added. If it's not zero, the SQLError() API should be called to determine the error. By looking at the IBM documentation for the other APIs, it should be possible for you to work out how SQLError() should be called.
    .
    Also, I didn't set any extra environment, connection or statement options. That's why SQL-naming is needed in the CREATE VIEW statement rather than system-naming. Again, the IBM documentation tells what can be done. The include files for C in the QSYSINC library can give details on values that are used for constants such as &SQL_NTS and &SQL_DROP. There are many values like those available for customization.
    .
    Once a database connection exists in an environment, any number of SQL statements can be constructed and executed. A CL job might set up an environment and a connection by calling into a service program early in the job. As the job runs, it might run many SQL statements by calling a service program procedure for statement execution. After all SQL is done, the cleanup procedures would be called.
    .
    Instead of simply creating a VIEW, V5R4 CL can do much more because *PTR (pointer) variables are allowed. That means you don't need to use RCVF at all to read rows from a declared file. It's not necessary to declare files because you can use the SQL CLI APIs to create, populate, read, insert, update and drop files on the fly if you wish. The files can be on the local system or in a remote database, as long as it's defined in the database directory.
    .
    After SQL CLI, you can also use the various _R* APIs from the ILE C runtime functions. With those, you can do anything you'll ever want to do with native record I/O.
    .
    And if you want those in an easy to use form, you can visit Bruce Vining's PowerCL site to see if you can make use of his offerings. He provides new CL commands that are created over some of the APIs mentioned above so that you don't have to.
    .
    At V5R4 and later, CL can be made to do just about everything any other language can do. If there are no other languages available on the system (other than REXX and MI, and maybe Java), CL might be a reasonable choice for some database work.
    .
    And in that case, OPNQRYF could be useful. But database access is better done in other languages. I've done it in all of them for many years, so I have some experience that guides that opinion.
    .
    Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following