Guru: Copy From Stream File FMTOPT(*NOCHK)
April 13, 2020 Bob Cozzi
Using SQL frequently, as I do, I tend to look for solutions to problems that can be resolved using the database language (i.e., SQL). Sometimes I have to invent a missing piece of the puzzle. Often that entails writing a User-Defined Function (UDF) for use in SQL, but sometimes a CL command is the better choice.
Recently I had a situation where what the client calls an “EDI File” was being downloaded using sftp on the IBM server running i. This file is created at a vendor that runs IBM mainframes (an actual mainframe by the way) and the file is created on their system as output from a COBOL program. The reason I know it is COBOL is because when they sent me the file layout, the specs where actually in COBOL.
For the last 18 years, this file has been retrieved using FTP to the various IBM i platform names every day without a hitch — I’d guess that’s nearly a record for EDI content. However, the days of using FTP are over and sftp is now required by most vendors. Sadly, unlike the FTP tool that runs natively on IBM i, the sftp client is PASE or QShell based. It also is “just a port” of sftp from (probably) IBM’s AIX operating system, which may have been itself ported from FreeBSD. Consequently, sftp does not do CCSID conversions. In fact, the IBM i PASE/QShell sftp doesn’t seem to support ascii/text file transfers, which is when the CCSID conversion could be performed. Effectively every transfer is “BINARY”. Which is not to say that only image or binary files may be transferred; sftp can transfer anything. It just doesn’t automatically do ASCII to EBCDIC translations. This means we have to change the code to handle this situation.
This story contains code, which you can download here.
At first, I thought I might be able to use sftp to download the file and then use something like CPYFRMIMPF or CPYFRMSTMF to convert the data to Db2 for i format. But this file had something weird, it had a multiformat, varying length record structure. In the past, we copied it to an interim flat file and then from there to an externally described file. So first we needed to get it into a flat file in the Db2 (or QSYS) file system.
In many cases, a file downloaded with sftp can still be copied to Db2 for i tables using CPYFRMIMPF file. But CPYFRMIMPF works best with CSV files. Although it does support flat files (it calls them *FIXED) it has some restrictions. In most other cases the CPYFRMIMPF command can be used and can also do the CCSID conversion for you. Sadly, not in my situation.
I did try using the Copy From Import File (CPYFRMIMPF) command but found that the Field Definition File (FLDDFNFILE) parameter was required when the DTAFMT(*FIXED) parameter is specified. I’m sure someone out there has used that parameter, but not me.
Then I looked at Copy from Stream File (CPYFRMSTMF) but found that CPDA082 “Must be a source file” was coming up referring to the target file, every time I tried to copy the data.
In the “old days” the Copy File (CPYF) command seemed to do everything and had a cool FMTOPT(*NOCHK) parameter to “make it work” when it didn’t. But today everyone is working in a heterogeneous environment, and therefore I couldn’t expect the original CPYF command to solve my problem with stream files; so I used the only option left: write my own.
I decided to write a program in C++ that would do what I wanted. It could have been written in C as well, or even RPG IV for that matter, but I mostly use C++ nowadays since 85 percent of the stuff I code for my products is written in C++.
My Copy Stream File (CPYSTMF) command copies the data in a stream file to a database file member. CCSID conversions are NOT performed as the requirement is for a “binary copy”. I needed the result to have the same data in it as the downloaded mainframe file. So I open both the input and output as “binary” files so no CCSID conversion is performed.
Recently I discovered I am one of only a few developers who create their own CL commands. Since I’ve always written my own commands, I just assumed it was a normal, wide-spread practice. To my surprise after a recent survey, I realized I’m more unique in this practice than I had thought. Therefore, I have to assume most of the readers don’t know Command Definition statements. But before I describe them, let’s look a picture of the prompt of this command:
To create this prompter, the system uses Command Definition Statements. Which are really just a handful of Command themselves that are used to define the parameters of Commands. Commands themselves are just a way to format parameters passed to program.
There are four basic Command Definition Statements (as they’re called) and only two are needed to create a simple Command.
- CMD – Command Definition Statement. Defines the prompt text at the top of the screen when the command is prompted. In later years, IBM added most of the CRTCMD parameters to the CMD statement itself-not the name of the program to run, but most others are there.
- PARM – Defines each Command Parameter. Parameters may be simply parameters, such as a 10-byte name or a 7-digit number, or they can be complex, like a qualified name or list of items. An example of a list of items is the LOG parameter of the SBMJOB command.
- ELEM – Defines one of the list of elements for a parameter. Each ELEM is like a subfield of a data structure when it is passed to the program.
- QUAL – Defines a qualify parameter. You’ve all seen qualified parameters, most commands have at least one. It is when you have a 2-, 3-, or more part name that is qualified with the forward slash. The OBJ parameter of DSPOBJD is one example, and the JOB parameter of any command is an example of a 3-part qualified parameter.
You can also mix these things up and get some cool parameter input results. For example, you might want something like FILE(LIBR/DATAF1 MBRNAME) for your parameter. Where the first part of it is a qualified name, and the second part is a simply 10-byte name.
As mentioned, you use the CRTCMD to compile Command Definition Statements in a *CMD object. At compile-time you need to specify the PGM (program) parameter. This is the Command Processing Program, or “program to run” when the command is used. That program receives all the parameters defined by the Command.
My CPYSTMF command (defined below) has five parameters.
- FROMSTMT – The name of the stream file (IFS file) to be copied.
- FILE – The qualified Db2 file name or the single value *STMF to indicate that you prefer to specify the target file using /QSYS.LIB/MYLIB.LIB/MFILE.FILE syntax.
- MBR – The name of the member of the FILE that receives the copied data.
- RCDLEN – The record length used when writing data to the target file. If 0 or RCDLEN(*FILE) is specified, the command processing program retrieves the file’s record length using the IBM QDBRTVFD API.
- DB2FILE – When you prefer to use /QSYS.LIB syntax for the target file name, this parameter may be used for that purpose. If you specify the target file name here, specify FILE(*STMF).
There is also a DEP statement in the Command Definition. This statement is used to ensure dependencies between parameters. For example, if you specify FILE(*STMF) then there must be a stream file (STMF) parameter entered, and it cannot be STMF(*FILE).
CPYSTMF: CMD PROMPT('Copy IFS to Db2')
PARM KWD(FROMSTMF) TYPE(*PNAME) LEN(650) MIN(1) +
EXPR(*YES) CASE(*MIXED) PROMPT('IFS file +
to copy')
PARM KWD(FILE) TYPE(QUAL) SNGVAL((*STMF)) MIN(1) +
PROMPT('File to receive data')
QUAL: QUAL TYPE(*NAME) LEN(10) EXPR(*YES)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*LIBL) (*CURLIB)) EXPR(*YES) +
PROMPT('Library')
PARM KWD(MBR) TYPE(*NAME) LEN(10) DFT(*FIRST) +
EXPR(*YES) PROMPT('Member name')
PARM KWD(RCDLEN) TYPE(*INT2) DFT(*FILE) REL(*GT +
0) SPCVAL((*FILE 0)) PROMPT('Maximum +
record length to write')
PARM KWD(DB2FILE) TYPE(*PNAME) LEN(650) +
DFT(*FILE) SPCVAL((*FILE)) EXPR(*YES) +
CASE(*MIXED) PROMPT('Db2 file to receive +
data')
DEP CTL(&FILE *EQ *STMF) PARM((&DB2FILE *NE *FILE))
Let look at the C++ code now. The CPYSTMF C++ program opens the stream file as a “binary” file and opens the Db2 for i database file for output in a similar way. The key section of the code is the WHILE loop. In that loop we inspect the data just read from the IFS file. We check if it is at least as long as the target Db2 for i record length, previously retrieved using the QDBRTVFD API or passed in by the user. If it is shorter, I pad the output buffer with x’00’, which may NOT be what you need, so your mileage may vary. To change the pad character to blanks, change the following line in the code as illustrated here:
From this:
CODE2
memset(line+bytesRead,0x00, rcdLen - bytesRead);
To this:
memset(line+bytesRead,0x40, rcdLen - bytesRead);
We wrote this to write to a file that is made up of character and/or zoned numeric data. Packed data could work as well, however are probably challenges with packed fields. So I recommend having an interim file with the same format as the final destination, but with the packed fields redefined as Zoned decimal and that zoned file being used as the target of this command. After CPYSTMF copies your data in the “zoned” file, use the good old CPYF command with FMTOPT(*MAP) to get it into the final format.
For the three to five of you out there programming for IBM i using C or C++, the C++ code that runs behind this command is available below. For the last several years, users have been downloading and using my COZTOOLS product at no change. About two years ago, I made COZTOOLS free for the first time in over 30 years; and it’ll stay free forever. In keeping with that philosophy, this new CPYSTMF command shall be added to the next refresh of the COZTOOLS library. But I’m including the source code here for your reference.
/****************************************************/
/* (c) Copyright Cozzi Productions, Inc. 2020 */
/* All rights reserved. */
/****************************************************/
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
#include
using namespace std;
typedef _Packed struct qtf
{
char file[10];
char library[10];
} qualFile_T;
int main(int argc, char* argv[])
{
std::ifstream ifsFile;
std::ofstream db2File;
std::string inFile;
std::string outFile;
std::string mbrName;
Qdb_Mbrd0100_t mbrDesc;
char overrides = '1';
char findMbr = '1';
char stmf[640];
Qus_EC_t ec;
Qdb_Qddfmt_t fmtInfo;
memset((char*)&fmtInfo,0x00,sizeof(fmtInfo));
char line[4096];
long rcdLen = 0;
int bStmf = 0;
int bytesRead = 0;
qualFile_T rtnFileLib;
qualFile_T dbf;
char mbr[10];
if (argc < 3)
{
Qp0zLprintf("syntax: copyifs \
(\'/ifsfolder/file.txt\' \'/QSYS.LIB/mylib.lib/myfile.file/mymbr.mbr\')");
return 1;
}
// Parameter Sequence:
// 1 = Source IFS Stream file
// 2 = Qualified Db2 File name and library
// 3 = Member Name (blank if none specified)
// 4 = /QSYS.LIB Db2 File name if FILE(*STMF) is specified.
memset((char*)&mbr, ' ', sizeof(mbr));
memset((char*)&ec,0x00,sizeof(ec));
ec.Bytes_Provided = sizeof(ec);
if (argc >= 2) // Stmf Name
{
inFile.reserve(640);
inFile.assign(argv[1],640);
size_t len = inFile.find_first_of(' ');
if (len != std::string::npos)
{
inFile.erase(len);
}
}
if (argc >= 3) // Qualified File Library
{
outFile.assign(argv[2],20);
}
// argc==4 is Member name
if (argc >= 5) // max record length to write to target file
{
rcdLen = *((short*) argv[4]);
}
if (stricmp(outFile.c_str(),"*STMF")==0 && argc >= 5)
{
outFile = argv[6];
bStmf = true;
}
else
{
memset((char*)&dbf, ' ', sizeof(dbf));
_CPYBYTES((char*)&dbf, outFile.c_str(), std::min(sizeof(dbf),outFile.length()));
if (argc >= 4)
{
mbrName.assign(argv[3],10);
_CPYBYTES(mbr,argv[3],std::min(sizeof(mbr),strlen(argv[3])));
}
}
if (!bStmf)
{
if (mbr[0] =='*' || mbr[0]==' ' ||
dbf.library[0]=='*' || dbf.library[0]==' ')
{
memset((char*)&ec, 0x00, sizeof(Qus_EC_t));
ec.Bytes_Provided = sizeof(Qus_EC_t);
memset((char*)&mbrDesc,0x00,sizeof(mbrDesc));
if (memicmp(mbr,"*FILE",5)==0)
{
_CPYBYTES(mbr,dbf.file,sizeof(mbr));
}
else if (memicmp(mbr,"*ONLY",5)==0 ||
memicmp(mbr,"*FIRSTMBR",9)==0 ||
mbr[0] == ' ')
{
_CPYBYTES(mbr,"*FIRST",strlen("*FIRST"));
}
if (dbf.library[0]==' ')
{
_CPYBYTES(dbf.library,"*LIBL",strlen("*LIBL"));
}
}
QUSRMBRD(&mbrDesc, sizeof(mbrDesc),"MBRD0100",&dbf,mbr,
&overrides,&ec,&findMbr);
if (ec.Bytes_Available == 0)
{
if (rcdLen == 0)
{
QDBRTVFD((char *) &fmtInfo, sizeof(fmtInfo),
&rtnFileLib, "FILD0200",
&dbf, "*FIRST ", &overrides,
"*LCL ", "*EXT ", &ec);
if (ec.Bytes_Available == 0)
{ // GET THE RECORD LENGTH
rcdLen = fmtInfo.Qddfrlen;
}
}
char f[11];
char l[11];
char m[11];
memset(f,0x00,sizeof(f));
memset(l,0x00,sizeof(l));
memset(m,0x00,sizeof(m));
_CPYBYTES(f,mbrDesc.Db_File_Name,sizeof(mbrDesc.Db_File_Name));
_CPYBYTES(l,mbrDesc.Db_File_Lib,sizeof(mbrDesc.Db_File_Lib));
_CPYBYTES(m,mbrDesc.Member_Name,sizeof(mbrDesc.Member_Name));
f[::triml(f, ' ')] = 0x00;
l[::triml(l, ' ')] = 0x00;
m[::triml(m, ' ')] = 0x00;
sprintf(stmf,"/QSYS.LIB/%s.lib/%s.file/%s.mbr",l,f,m);
outFile = stmf;
}
}
ifsFile.open(inFile.c_str(),ios_base::_occsid | std::ifstream::in);
std::transform(outFile.begin(), outFile.end(), outFile.begin(), ::toupper);
db2File.open(outFile.c_str(), ios::out|ios::binary);
if (ifsFile.is_open()) // if is open...
{
while (!ifsFile.eof())
{
ifsFile.getline(line,sizeof(line)-1);
bytesRead = ifsFile.gcount();
if (bytesRead > 0)
{
if (rcdLen > 0 && rcdLen < bytesRead)
{
bytesRead = rcdLen;
}
else
{
bytesRead--;
if (rcdLen > 0)
{ // pad the line to the full requested record length
if ( bytesRead < rcdLen)
{
memset(line+bytesRead,0x00, rcdLen - bytesRead);
}
bytesRead = rcdLen;
}
}
db2File.write( line, bytesRead );
}
}
}
ifsFile.close();
db2File.close();
return 0;
}
The #includes in your C++ source didn’t include what was supposed to be included. That is, they appear not to be inclusive.