ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Get record count of logical in CL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Get record count of logical in CL

    What is the simplest way to get a current number of records of a file through a logical view in CL? My LF was created via an SQL View. I'd like something simple like the RTVMBRD with NBRCURRCD parameter.

  • #2
    Using the RTVMBRD with NBRCURRCD parameter against the view will give you the number of records in the underlying table, but in certain cases (such as if the view has a WHERE clause) that might not be the number you're looking for. In those cases, one way to do it is to run an SQL statement against the view and output the results to a temporary table [RUNSQL SQL('Create Table QTEMP/temptable as (select * from viewname) with data')], then use RTVMBRD against the temporary table.

    Comment


    • #3
      You can also have a view that you read from...

      Code:
      create view file_count as select count(*) cnt from file where status='A';
      View file_count will have one record with a value showing all records with status = 'A' - or you can not have a where clause and get a count of all records.

      Comment


      • #4
        How to place the output of a SQL Select into a CL Variable
        - Execute the SQL Select using QSH/db2
        - Write the results to a data area
        - Retrieve the data area into a CL Variable

        (With a big Thank You to Scott Klement)

        QSH Utilities:
        - db2 - run SQL statements (uses SQL naming only)
        - grep - string search, used to identify the actual line of data in the output
        - cut - like substring, cuts away the data line marker
        - datarea - (not a misspelling) writes to a data area
        - pipe | - the output of one command becomes the input to another command

        Special Character Usage:
        - single quotes around the entire command
        - double quotes around the SQL statement
        - 2 single quotes on each side of the double equals (used for grep searching)
        - double pipes used to perform concatenation

        Special Note:
        - Limit the SQL statement to return 1 row only
        - Don't set QIBM_QSH_CMD_OUTPUT to NONE, the output is piped to the grep utility
        - Verify that your data area is large enough to receive the SQL Select output
        - We're on v7r3 tr6


        Pgm

        Dcl Var(&Cmd) Type(*Char) Len(500)
        Dcl Var(&RecCnt) Type(*Char) Len(35)

        ChgVar Var(&Cmd) +
        Value('db2 +
        "select ''=='' || char(count(*)) +
        from MyDataLib.MyLogical " +
        | grep == | cut -b 3- +
        | datarea -w /qsys.lib/mylib.lib/mydtaara.dtaara')

        QSh Cmd(&Cmd)
        MonMsg MsgId(QSH0000)

        RtvDtaAra DtaAra(MyLib/MyDtaAra *ALL) RtnVar(&RecCnt)

        SndUsrMsg Msg(&RecCnt) MsgType(*Info)

        EndPgm

        Comment

        Working...
        X