ISeries AS/400 AS400 Command PRTVMSF Retrieve File from MSSQL (Microsoft SQL Server)

PRTVMSF Command for IBM ISeries AS/400 AS400

Introduction

PRTVMSF retrieves a file from a Microsoft Sequel Server (MS SQL) database and places the records in an equivalent database file on your ISeries (AS/400, AS400) machine. All required software runs on the ISeries - no additional software is required on the Windows system running MSSQL. This is one of the commands in our ISeries utilities library (click this URL for full contact and pricing details).

This command uses a 3rd-party JDBC (Java Database Connectivity) package to read the data from the MSSQL database. The package we use is called jtds (http://sourceforge.net/projects/jtds/). You do not have to know anything about Java to use this package. Simply download and deploy this package as described below.

The files on both sides (MSSQL and ISeries) must have comparable record layouts. The layouts do not have to be identical e.g. the MSSQL table may have a 'Decimal' field type that maps to a 'Zoned Decimal' field on ISeries. Virtually all of these field mappings are sorted out for you because the PRTVMSF command uses intermediate CSV (comma-separated value) records - the actual import of data into your ISeries file is done using our PCPYFRMCSV command.

Preparation

Before you consider using the PRTVMSF command, you need to do the following:

Establish MSSQL availability: Get the IP address nnn.nnn.nnn.nnn of the PC that hosts the MSSQL database. At the command line on your ISeries type ping rmtsys(*intnetadr) intnetadr('nnn.nnn.nnn.nnn'). You should see a message something like Ping reply 1 from nnn.nnn.nnn.nnn took xxx ms. If you see any other message from the ping test, refer this to your ISeries technical support team.

Establish MSSQL connection details: Get the name of the MSSQL database, the name of the table in the database you want to access, and a user name and password that has read authority to the database. Note these values may be case-sensitive. Also establish if the PC database server job that services database requests is listening on the default port for MSSQL (port number 1433) or some other port.

Test that your ISeries supports Java: Type java *version on the command line on your ISeries. You should see a JVAB52C message like Version JVM VxRxMx JDK 1.3.1 If you see any other message, then you need to install basic Java support on your ISeries. Refer this to your ISeries technical support team. The JDK version (1.3.1 in example above) is recommended as follows:

Anything less than 1.3: You must upgrade your ISeries JVM to at least 1.3.x

1.3.x or 1.4.x:: The jtds web site claims good compatibility with either of these versions. Highly unlikely you need to upgrade your ISeries JVM.

1.5.x or later: Should be Okay, provided the quite old version of jtds.jar we provide uses java classes that are backwards compatible from 1.5.x to either 1.3.x or 1.4.x. We can not be absolutely certain of this (we did'nt write the jtds code), but it is highly likely Sun has addressed any backwards compatibility isues.

Get Java clases: Download our prtvmsf.class file. Also download jtds-0.7.1.jar. If you encounter problems with this jar file, you may be able to get a later version from jtds.

Deploy the above two Java files on your AS/400: Use the WRKLNK command to find a suitable directory for 2 files. If in doubt, we suggest you create a new directory /usr/psputil/ and place the two files there. Grant read authority to *PUBLIC to the two files. Also grant *PUBLIC write authority to any new directory you create.

Command Parameters

The PRTVMSF command has the following parameters:

DTAFIL The name of the file (qualified by library) on the ISeries where the retrieved MSSQL records are to be placed.

MSIP The IP address of the system hosting the MSSQL database.

MSDB The name of the MSSQL database on the above system.

MSFIL The name of the file (table) in the above database whose records are to be retrieved.

MSUSR
MSPWD The name and password of the PC user that has read access to the MSSQL database.

MSPORT The IP port the MSSQL database server job is listening on.

MSAPP Application name. This appears as part of an audit entry in MSSQL logs. Does not control access to MSSQL.

DTADIR The ISeries directory where you deployed the two java files.

JTDSJAR The name of the jtds jar file you deployed.

REPLACECR The single character to replace CarriageReturn (hexadecimal X'0D') characters encountered in text/character fields in the MSFIL table. A null value (X'00') means that CarriageReturn characters are ignored i.e. do not appear in ISeries records.

REPLACELF The single character to replace LineFeed (hexadecimal X'0A') characters encountered in text/character fields in the MSFIL table. A null value (X'00') means that LineFeed characters are ignored i.e. do not appear in ISeries records.

CSVDLM
CSVSEP
CSVEOR
CSVEORCHAR The records retrieved from MSSQL are converted internally into CSV format. They are then updated to the DTAFIL file on your ISeries using the PCPYFRMCSV command. Intermediate CSV files have options for: 1) CSVDLM - the character used to delimit alphanumeric fields, 2) CSVSEP - the character used to separate individual fields, 3) CSVEOR and CSVEORCHAR - the characters used to indicate end-of-record.

Examples

PRTVMSF DTAFIL(QGPL/INVPF) MSIP('217.23.164.27') MSDB(ACCOUNTS) MSFIL(INVOICES) MSUSR(ISERIES) MSPWD(SECRET) MSAPP(billing)

Command above retrieves records from a MSSQL database that resides on the system with IP address 217.23.164.27, with the database server listening on the default MSSQL port number 1433. The table INVOICES in the MSSQL database ACCOUNTS is accessed with user ISERIES and password SECRET. Records retrieved are copied into the INVPF file in the QGPL library on the ISeries. The word billing will appear in MSSQL logs for this connection. Default values are used for all optional parameters.

PRTVMSF DTAFIL(CUSTLIB/ADRP) MSIP('192.168.20.10') MSDB(customer) MSFIL(address) MSUSR(test1) MSPWD(password) MSPORT(1434) DTADIR(/usr/test/) JTDSJAR(jtds-0.8.jar) REPLACECR(X'00') REPLACELF('&') CSVDLM(' ') CSVSEP('|') CSVEOR(*CHAR) CSVEORCHAR(X'0B')

Command above retrieves records from a MSSQL database that resides on the system with IP address 192.168.20.10, with the database server listening on port number 1434. The table address in the MSSQL database customer is accessed with user test1 and password password. Records retrieved are copied into the ADRP file in the CUSTLIB library on the ISeries. The jtds jar file used is jtds-0.8.jar rather than the default jtds-0.7.1.jar. This file is located in the ISeries directory /usr/test/ rather than the default /usr/psputil/. CarriageReturn characters in source data text fields are dropped. LineFeed characters in source data text fields are replaced with the ampersand '&' character. In intermediate CSV files: 1) blank characters are used to delimit alphanumeric fields, 2) the bar character '|' is used to separate fields, 3) the hexadecimal character X'0B' is used to indicate end of record.

Restrictions

1) The first member in the target ISeries file is always used. This member is cleared (CLRPFM) before the MSSQL database is accessed. If you want more flexibility, use an intermediate empty copy of the ISeries file then run your own selective CPYF.

2) The command parameters passed down to MSSQL itself (MSDB, MSFIL, MSUSR, MSPWD, MSAPP) may be case-sensitive. Confirm with your MSSQL admininistrator the exact case for these variables.

3) Character fields in MSSQL - particulary 'Text' fields - may be multiline e.g. have mutiple address lines each usually terminated by the CR/LF (CarriageReturn/LineFeed) combination of characters. These two characters cause problems with CSV import of data into your ISeries file. Use the REPLACECR and REPLACELF parameters to specify replacement characters. A null value (X'00') means that PRTVMSF drops the character altogether.

4) The DTAFIL file must have the same number of fields as the MSFIL table. Character fields in DTAFIL may be shorter than their matching fields in MSFIL - in which case excess RHS characters are truncated. If you want to ignore a character field in MSFIL, create a minimal 1A field equivalent in DTAFIL.

5) Conversion of data values in individual fields is determined by the PCPYFRMCSV command. See that command for restrictions.

6) When PRTVMSF runs, two intermediate files are used. A stream file is placed in the same directory as prtvmsf.class. The name of this file is xxx_nnnnnn_CSV where xxx is the value of the MSFIL parameter and nnnnnn is the 6-digit number of the ISeries job running the PRTVMSF command.
A database file is also created in the QTEMP library of the ISeries job running the PRTVMSF file. The name of this file is CSV_nnnnnn. This file is created with the same SIZE() as DTAFIL. So you should typically create DTAFIL with a SIZE() that caters for the number of records you expect to retrieve.
If the PRTVMSF command completes normally, both these intermediate files are deleted. If these files persist, it may indicate a problem that you/we should investigate.

7) When PRTVMSF runs, a spool file is created for the execution of prtvmsf.class. This normally contains one line 'Java program completed'. If PRTVMSF completes normally, it deletes this spool file. If the spool file persists, look at the contents of the spool file to see if it indicates a problem that you/we should investigate.

IBM rebranded the AS/400 - some users called it AS400 - to ISeries several years ago. All 3 names - ISeries, AS/400, AS400 - refer to exactly the same system.