Line count of IFS file

[vinomails]

Hi All,

do we have any SQL query to find the no of lines in a IFS stream file?

[JonBoy]

I doubt it. IFS files don't have "lines". They are just a big stream of bytes and no information is stored as to what record termination was used. CR/LF, LF only, fixed length, null termination, etc. etc. About the best you can probably do is get the size and (assuming you know) divide that by record length.

But why do you need to know?

[Scott Klement]

As Jon points out "IFS stream file" doesn't mean much.

But, assuming you mean a file that follows text file conventions (that would be the norm for something that has 'lines'), you could get the line count in QShell very easily:

STRQSH CMD('wc -l myfile.txt')

Not sure if you can do something like this from SQL, as I'd never tried or wanted to do it from SQL.

[B.Hauser]

Assumed your lines are ended with CRLF (x'0D25'), you could use the SPLIT Function (in the SYSTOOLS schema), to split the IFS file into rows and then count the rows.
Something like this:

Select Count(*)
  from Table(SysTools.Split(Qsys2.Get_Clob_From_File('/Home/YourDir/YourIFSFile.txt'), x'0D25')) x;

Birgitta

[Vectorspace]

In other languages, split() on an empty string will return one empty line. Which your logic would result in a lines count of 1, when I suspect we'd want 0 for an empty file? I don't know if split on i does that - I'm only on i7.2 so I can't try it myself.

[vinomails]

unfortunately, i don't have split in my system.

[Vectorspace]

My first thought was to use regexp_count() to count the number of newlines. My instinct is that would be more efficient than split, but I don't know for sure. Split is not available on i7.2, so if you (like me) are not on 7.3 or 7.4 yet, regexp_count is an alternative.

regexp_count() also needs additional work - since the number of lines is 1+ the number of newline characters, except if the file is empty in which case we want lines = 0, not 1.

So I end up with this:

values(
  case when Get_Clob_From_File('/Home/SPEED#J/linestest.txt') = '' then 0
       else regexp_count(Get_Clob_From_File('/Home/SPEED#J/linestest.txt'),'\n') + 1
       end
)

Also, bear in mind that Get_Clob_From_File() requires commitment control to be enabled for the statement

[gcraill]

Hola!

What am I missing here, running 7.2 and getting errors ... trying to get this reading of IFS files thing happening, came across this thread as a start point.

Set Option Commit=*CHG;
select x.* from table(qsys2.GET_BLOB_FROM_FILE('/home/ctm/CMAGENT700/DAILYLOG/ctmag_20200702.log',1)) as x;
Set Option Commit=*NONE; 

SQL State: 42887
Vendor Code: -390
Message: [SQL0390] Use of function GET_BLOB_FROM_FILE in QSYS2 not valid. Cause . . . . . : Use of function GET_BLOB_FROM_FILE in schema QSYS2 is not valid. The specific name is GET_BLOB_FROM_FILE_2. One of the following has occurred: -- A table function was specified in a clause other than the FROM clause. -- A function was specified in the FROM clause but the function is not a table function. -- A table function was specified as a source function in a CREATE FUNCTION statement. -- UNNEST was specified but is only allowed in SQL procedures and functions. -- WITH RETURN was specified for a cursor that has a function with an array result in the select-list. Recovery . . . : Remove the function from the clause or change the function name, arguments, or path so that a different function is found. Try the request again.

GC

[Vectorspace]

GET_BLOB_FROM_FILE() is not a table function, so it does not return a table-like result set you can directly parse with FROM TABLE()

It's a scalar function, it returns a single value like TRIM() does.

In an interactive SQL view you can display the output by using values() rather than select:

values(qsys2.GET_BLOB_FROM_FILE('/home/ctm/CMAGENT700/DAILYLOG/ctmag_20200702.log',1);

If it absolutely must be a select, you can use values() within table()

select * from table(values(qsys2.GET_BLOB_FROM_FILE('/home/ctm/CMAGENT700/DAILYLOG/ctmag_20200702.log',1))) as blobTable (blobCol);

Where blobCol is the column name it will have in the final result set.

But there are (in my opinion) better ways of extracting BLOB/CLOB data from the IFS in SQL, depending on the context - what purpose does this have for you? Are you looking to import this into a table BLOB type column? Into an SQLRPGLE host variable? Something else? Or just view it in an interactive SQL view?

Also, please note that GET_BLOB_FROM_FILE() requires commitment control because under the hood it uses a LOB Locator, which is not freed up until a COMMIT or ROLLBACK. I don't know, but I don't think Set Option Commit=*NONE; counts, so you should COMMIT/ROLLBACK immediately after GET_BLOB_FROM_FILE()

[Viking]

CPYFRMIMPF from the stream file to a PF, and then select count(*) on that PF.