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.
Announcement
Collapse
No announcement yet.
Get record count of logical in CL
Collapse
X
-
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.
-
You can also have a view that you read from...
Code:create view file_count as select count(*) cnt from file where status='A';
Comment
-
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
Comment