sponsored links



No announcement yet.

Get record count of logical in CL

  • Filter
  • Time
  • Show
Clear All
new posts

  • Herb...
    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


    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)


    Leave a comment:

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

    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.

    Leave a comment:

  • Brian Rusch
    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.

    Leave a comment:

  • CarrieGig
    started a topic Get record count of logical in CL

    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.