EBCDIC Files on the Mainframe
If mainframe files are part of your testing scheme, there are several steps that may be needed in order to incorporate them into your QuerySurge execution runs. First, files need to be moved from the mainframe to a network location where a QuerySurge Agent can access them. Second, mainframe files are typically encoded using the IBM EBCDIC standard. In order for QuerySurge to access the data in such files, the files need conversion to an ASCII format. This procedure can usually be fully automated, via the custom "plug-in" feature of QuerySurge's flat file JDBC driver. This article shows the basic setup for an automated EBCDIC-to-ASCII conversion followed by a query against the ASCII version of the file.
In this example, we use a small EBCDIC file with a simple fixed-width layout to show how to set up EBCDIC-to-ASCII conversions via a custom plug-in function. For the conversion, we'll use IBM's JTOpen library, an open source IBM Toolbox for Java access to IBM i resources.
Note: You can find general project information about the JTOpen toolbox here. The download for the toolbox is here. After you download the zip, extract only the top-level jt400.jar file and deploy it to your project classpath.
In addition, we'll use the "custom function" feature of the QuerySurge Flat File JDBC driver. You can familiarize yourself with this feature through this worked example of a custom function.
Note: Because EBCDIC-to-ASCII conversions may be complex (since there are multiple EBCDIC code pages, and because the underlying file structure may be complex) it is helpful to know whether a conversion tool or process is already available in your organization. If a conversion protocol is already available, usually it is simplest to utilize the existing conversion.
The Custom Conversion
The JTOpen library contains a class which has methods for the EBCDIC-to-ASCII conversion. In addition to calling the conversion method, the custom conversion will need to manage the encoding of the input EBCDIC and output ASCII files, and will need to impose the record size of the fixed-width structure of the ASCII representation of the file.
Note that the EBCDIC file used in this example is structurally simple - EBCDIC files may be quite complex in structure, and any conversion you use will have to reflect the complexity of the file. In addition, it is important to note that the EBCDIC file is saved as a UTF-8 encoded file. This encoding information will be important when we consider the file handling.
The Conversion Method
private static String ebcdicToAscii(String ebcidicFromFile, Charset charSet)
throws Exception {
byte [] ebcdicByteArr = ebcidicFromFile.toString().getBytes(charSet);
int textLen = ebcdicByteArr.length;
AS400Text textConverter = new AS400Text(textLen);
String asciiBuffer = ((String)textConverter.toObject(ebcdicByteArr)).
substring(0, textLen);
return asciiBuffer;
}The AS400Text class handles all of the details of the actual conversion; the remaining lines of the method set up the conversion and return the ASCII data. Also, note that an encoding (Charset) must be specified to convert the EBCDIC string to a byte array for processing by the AS400Text method toObject().
Writing the ASCII File
Finally, we'll need a method that will parse the converted ASCII into records of pre-determined length, and write the result to a file. Note that the line endings used here are Linux-style '\n' line endings, not Windows '\r\n' line endings.
private static void writeAsciiBufferToFile(
String asciiData,
String asciiFilePath,
int recLenInBytes,
Charset charSet,
boolean addLinebreaks) throws IOException {
StringBuilder asciiBuffer = new StringBuilder(asciiData);
if (addLinebreaks) {
int pos = 0;
while (true) { // build ascii buffer with line breaks
pos += recLenInBytes;
asciiBuffer.insert(pos, '\n');
pos++;
if (pos >= (asciiBuffer.length() - recLenInBytes)) {
break;
}
}
List<String> asciiDataAsList = new ArrayList<String>(Arrays.asList(asciiBuffer.toString().split("\n")));
Files.write(Paths.get(asciiFilePath), asciiDataAsList, charSet);
} else {
Files.write(Paths.get(asciiFilePath), asciiData.getBytes(charSet));
}
}A Helper Method
Because we need to call into this code from SQL, arguments must either be string types or numeric types. Therefore, we need a method to convert the string-based names of encodings (Charsets) to the actual Charset objects:
private static Charset charsetResolver(String charsetName) {
Charset charset = null;
switch (charsetName) {
case "UTF-8":
charset = StandardCharsets.UTF_8;
break;
case "UTF-16":
charset = StandardCharsets.UTF_16;
break;
case "UTF-16BE":
charset = StandardCharsets.UTF_16BE;
break;
case "UTF-16LE":
charset = StandardCharsets.UTF_16LE;
break;
case "US-ASCII": case "USASCII":
charset = StandardCharsets.US_ASCII;
break;
case "ISO-8859-1": case "ISO_8859_1":
charset = StandardCharsets.ISO_8859_1;
break;
}
return charset;
}The Wrapper Method
The first step in the wrapper method is to read in the EBCDIC file. As we noted above, the EBCDIC file is UTF-8 encoded, so we read it using this encoding. This is a relatively simple task in terms of code. The next step is to run the EBCDIC-to-ASCII conversion, specifying the encoding for the output. Finally, the ASCII buffer is written out to a file.
public static void ebcdicToAsciiConvert(String inputFilePath, String outputAsciiFilePath, int recLenInBytes, String inCharsetName, String convCharsetName, String writeCharsetName, boolean addLinebreaks) throws IOException {
// READ EBCDIC FROM FILE
Charset inCharset = charsetResolver(inCharsetName);
String ebcdicFromFile = new String(Files.readAllBytes(Paths.get(inputFilePath)), inCharset);
// CONVERT EBCDIC TO ASCII
Charset convCharset = charsetResolver(convCharsetName);
String ebcdicDataFromFileToAscii = ebcdicToAscii(ebcdicFromFile, convCharset);
// WRITE ASCII FILE
Charset writeCharset = charsetResolver(writeCharsetName);
writeAsciiBufferToFile(ebcdicDataFromFileToAscii, outputAsciiFilePath, recLenInBytes, writeCharset, addLinebreaks);
}The Agent Setup
Your Agent(s) require setup for the custom function. As noted above, we have a Knowledge Base article that shows the setup in more detail. In brief, you'll need to create a jar file with the custom function (see Resources at the end of the article for sample code) and the jt400.jar file from the JTOpen library (you'll need to download this) to each Agent's jdbc directory.
Then, you'll need to modify each Agent's agentconfig.xml to add the following tag under <connectionProps>. (It is advisable to make a copy of the original file before modifying.)
<connectionProps> ... <driverProp driver="jstels.jdbc.csv.CsvDriver2" prop="function:ebcdicToAsciiConvert" type="void" value="com.rttsweb.querysurge.EbcdicToAsciiConv.ebcdicToAsciiConvert"/> ...</connectionProps>
The Connection to the ASCII Flat File
The Connection that you'll need is for the converted ASCII flat file. Once this file is converted by the custom function, it will be a fixed-width ASCII file with 5 columns. The setup for the file in the Connection Wizard is standard in all respects (see here for fixed width flat file Connection configuration). In the Connection Wizard, the 5 ASCII file columns are defined by the file positions:
1-15,16-23,24-43,44-60,61-67
The column headers that we'll use are:
last_name, first_name, city, state, ID
The ASCII Flat File Query
The flat file query that we'll has two elements, a call to the custom function to convert the file from EBCDIC to ASCII, and a query against the converted file. The conversion function takes six arguments:
- A path for the source EBCDIC file
- An output path for the converted ASCII file
- A record length for the ASCII file rows
- The file encoding (Charset) for the EBCDIC file
- An encoding (Charset) for the converted ASCII characters (typically "ISO-8859-1")
- The file encoding (Charset) for the output file ("UTF-8" is commonly used)
- A boolean value of true or false for add line breaks
As indicated previously, the EBCDIC file is UTF-8 encoded, so we'll need to specify this in the call. For the output ASCII file, we'll use the ISO-8859-1 encoding. The record length for the sample file is 67 bytes. Note that EBCDIC files have no line terminators, but they can conveniently be added during the conversion.
When QuerySurge executes this query, it will find and read the EBCDIC file using a UTF-8 encoding to read the file, convert the file to ASCII and write it out as the file "ascii.txt" using ISO-8859-1 encoding, with a record length of 67 bytes. Then the SELECT will run against the converted file:
In the Resources section below, you can find files to help set up this example in your QuerySurge instance, including sample conversion code to help you build your jar and the source ebcdic.txt file. In addition, a copy of the generated (and queried) ASCII file (ascii.txt) is available for download.
Resources