unzipping a file on the iseries

as400 iseries Java ajar tool

The ajar tool is an alternative interface to the jar tool that you use to create and manipulate Java(TM) ARchive (JAR) files. You can use the ajar tool to manipulate both JAR files and ZIP files on the as400.

If you need a ZIP interface or UNZIP interface on the iseries, use the ajar tool instead of the jar tool.

The ajar tool lists the contents of JAR files, extracts from JAR files, creates new JAR files, and supports many of the ZIP formats just as the jar tool does. Additionally, the ajar tool supports adding and deleting files in existing JAR files.

The ajar tool is available using the iseries Qshell Interpreter. For more details, see ajar - Alternative as400 Java archive.

How do you re-enable the user id for STRSST when it has been disabled?

Use the CHGDSTPWD command. Simple!

Connecting from iseries RPG to a web browser

Use hotspots in your as400 Client Access session to do this

A hotspot is an area in the iseries PC5250 display session window where you can use your left mouse button to execute a command or function. Hotspots are generally used for the following functions:
• To simulate function keys with point-and-click technology
• To execute a macro by clicking on a word on your PC5250 display screen
• To execute a word or string on your PC5250 screen as if you had typed it in as an AS/400 command or option
• To provide a point-and-click facility by simulating the Enter key at the current cursor location
• To interface PC5250 with your Web browser by launching Web sites directly from your as400 OS/400 display session

http://www.mcpressonline.com/networking/general/fire-up-your-mouse-with-pc5250s-hotspots-feature.html

More as400 connectivity tips to follow

Difference between SFLINZ and SFLCLR keyword?

One effectively sets all the existing subfile records to blanks and zeros, the other removes existing records

SFLINZ (Subfile Initialize) keyword for display files

Use this record-level keyword on the subfile control record format to specify that the OS/400 program is to initialize all records in the subfile on an output operation to the subfile control record format (identified by the SFLCTL keyword). The fields in each subfile record are initialized to blanks for character type fields, to nulls for floating-point type fields, to zeros for other numeric type fields, or to the constant value specified on input-only fields if the DFT keyword is specified.

When the subfile is displayed (on an output operation to the subfile control record), all records in the subfile are displayed with the same value. Any record previously written is overwritten and no longer has its earlier value

SFLCLR (Subfile Clear) keyword for display files

Use this record-level keyword on the subfile control record format so that your program can clear the subfile of all records. This keyword differs from the SFLDLT keyword in that the subfile is not deleted. It differs from the SFLINZ keyword in that after being cleared, the subfile contains no data. Clearing the subfile does not affect the display. However, after being cleared, the subfile contains no active records.

This keyword has no parameters.

When active records already exist in the subfile and all are to be replaced, your program can send an output operation to the subfile control record format after selecting SFLCLR. This clears the subfile and permits your program to write new records to the subfile (by issuing output operations to the subfile record format while incrementing the relative record number). Issuing an output operation to an already active subfile record causes an error message to be returned to your program.

If SFLCLR is in effect on an output operation and no records exist in the subfile, SFLCLR is ignored

IBM iSeries follows in HP 3000 footsteps?

Start retraining now guys, I think the answer is very much a yes!

http://3000newswire.blogs.com/3000_newswire/2009/08/ibm-iseries-follows-in-3000-footsteps.html

The bottom line is we all need to face the facts of what is happening. IBM i, the OS, is gradually fading away based on IBM not selling it. Since there is no longer an organization to sell IBM i there can be no turn around or return to prominence

the demise of the OS400 community -- and so the iSeries computer line and its vendors -- seems inevitable to Cancilla

"It will simply continue to decline in users and will most definitely be dropped by IBM when the revenue reaches a point where it is no longer feasible to continue supporting it," he wrote in a blog entry last week. Cancilla is getting a strong reaction from iSeries advocates, according to the IT Jungle's Dan Burger. But that probably won't change the sales effort at IBM. This is an aspect of a vendor's disaffection with a platform that was never played out in public for the HP 3000. Right up to the announcement of HP's exit, the vendor and its partners never broached the prospect of HP giving up on the 3000.

But the AS400 (or iSeries or System i) saw a 40 percent decline in sales from '07 to '08, according to the IT Jungle. IBM has reduced the profile of the most i-like parts of the system when it moved System Licensed Internal Code and a Technology Independent Machine Interface into a new Virtualization Engine

iSeries DB2 catalog tables and views

iSeries catalog tables and views

The iSeries™ catalog includes the following views and tables in the QSYS2 schema:

DB2 UDB for iSeries name Corresponding ANSI/ISO name Description
SYSCATALOGS CATALOGS Information about relational databases
SYSCHKCST CHECK_CONSTRAINTS Information about check constraints
SYSCOLUMNS COLUMNS Information about column attributes
SYSCST TABLE_CONSTRAINTS Information about all constraints
SYSCSTCOL CONSTRAINT_COLUMN_USAGE Information about the columns referenced in a constraint
SYSCSTDEP CONSTRAINT_TABLE_USAGE Information about constraint dependencies on tables
SYSFUNCS ROUTINES Information about user-defined functions
SYSINDEXES  Information about indexes
SYSJARCONTENTS  Information about jars for Java™ routines.
SYSJAROBJECTS  Information about jars for Java routines.
SYSKEYCST KEY_COLUMN_USAGE Information about unique, primary, and foreign keys
SYSKEYS  Information about index keys
SYSPACKAGE  Information about packages
SYSPARMS PARAMETERS Information about routine parameters
SYSPROCS ROUTINES Information about procedures
SYSREFCST REFERENTIAL_CONSTRAINTS Information about referential constraints
SYSROUTINES ROUTINES Information about functions and procedures
SYSROUTINEDEP ROUTINE_TABLE_USAGE Information about function and procedure dependencies
SYSSEQUENCES  Information about sequences
SYSTABLEDEP  Information about materialized query table dependencies
SYSTABLES TABLES Information about tables and views
SYSTRIGCOL TRIGGER_COLUMN_USAGE Information about columns used in a trigger
SYSTRIGDEP TRIGGER_TABLE_USAGE Information about objects used in a trigger
SYSTRIGGERS TRIGGERS Information about triggers
SYSTRIGUPD TRIGGERED_UPDATE_COLUMNS Information about columns in the WHEN clause of a trigger
SYSTYPES USER_DEFINED_TYPES Information about built-in data types and distinct types
SYSVIEWDEP VIEW_TABLE_USAGE Information about view dependencies on tables
SYSVIEWS VIEWS Information about definition of a view

Need to access MS SQL databases from the as400. Use Microsofts JDBC driver

Overview of the JDBC Driver

The Microsoft SQL Server JDBC Driver is a Java Database Connectivity (JDBC) 4.0 compliant driver that provides robust data access to Microsoft SQL Server 2000, SQL Server 2005, and SQL Server 2008 databases. The JDBC driver can access many of the new features of SQL Server 2005, including database mirroring; the xml, user-defined, and large-value data types; and it supports the new "snapshot" transaction isolation. In addition, the JDBC driver also supports the use of integrated authentication with SQL Server 2000, SQL Server 2005, and SQL Server 2008. Note that the Microsoft SQL Server JDBC Driver version 2.0 can connect to SQL Server 2008 but does not support the new data types or other features that are new in SQL Server 2008.

Using the JDBC Driver
This section provides quick start instructions for making a simple connection to a SQL Server database by using the Microsoft SQL Server JDBC Driver. Before you connect to a SQL Server database, SQL Server must first be installed on either your local computer or a server, and the JDBC driver must be installed on your local computer.

Choosing the Right JAR file

The Microsoft SQL Server JDBC Driver version 2.0 provides sqljdbc.jar and sqljdbc4.jar class library files to be used depending on your preferred Java Runtime Environment (JRE) settings. For more information about which JAR file to choose, see System Requirements for the JDBC Driver.

Setting the Classpath

The JDBC driver is not part of the Java SDK. If you want to use it, you must set the classpath to include the sqljdbc.jar file or the sqljdbc4.jar file. If the classpath is missing an entry for sqljdbc.jar or sqljdbc4.jar, your application will throw the common "Class not found" exception.

The sqljdbc.jar file and sqljdbc4.jar file are installed in the following location:

\sqljdbc_\\sqljdbc.jar

\sqljdbc_\\sqljdbc4.jar

The following is an example of the CLASSPATH statement that is used for a Windows application:

CLASSPATH =.;C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_2.0\enu\sqljdbc.jar

The following is an example of the CLASSPATH statement that is used for a Unix/Linux application:

CLASSPATH =.:/home/usr1/mssqlserverjdbc/Driver/sqljdbc_2.0/enu/sqljdbc.jar

You must make sure that the CLASSPATH statement contains only one Microsoft SQL Server JDBC driver, such as either sqljdbc.jar or sqljdbc4.jar.

Note:
On Windows systems, directory names longer than 8.3 or folder names with spaces may cause problems with classpaths. If you suspect these types of issues, you should temporarily move the sqljdbc.jar file or the sqljdbc4.jar file into a simple directory name such as C:\Temp, change the classpath, and determine whether that addresses the problem.
Applications that are run directly at the command prompt
The classpath is configured in the operating system. Append sqljdbc.jar or sqljdbc4.jar to the classpath of the system. Alternatively, you can specify the classpath on the Java command line that runs the application by using the java -classpath option.

Applications that run in an IDE
Each IDE vendor provides a different method for setting the classpath in its IDE. Just setting the classpath in the operating system will not work. You must add sqljdbc.jar or sqljdbc4.jar to the IDE classpath.

Servlets and JSPs
Servlets and JSPs are run in a servlet/JSP engine such as Tomcat. The classpath must be set according to the servlet/JSP engine documentation. Just setting the classpath in the operating system will not work. Some servlet/JSP engines provide setup screens that you can use to set the classpath of the engine. In that situation, you must append the correct JDBC Driver JAR file to the existing engine classpath and restart the engine. In other situations, you can deploy the driver by copying sqljdbc.jar or sqljdbc4.jar to a specific directory, such as lib, during engine installation. The engine driver classpath can also be specified in an engine specific configuration file.

Enterprise Java Beans
Enterprise Java Beans (EJB) are run in an EJB container. EJB containers are sourced from various vendors. Java applets run in a browser but are downloaded from a Web server. Copy sqljdbc.jar or sqljdbc4.jar to the Web server root and specify the name of the JAR file in the HTML archive tab of the applet, for example, applet ... archive=sqljdbc.jar>.

Making a Simple Connection to a Database

Using the sqljdbc.jar class library, applications must first register the driver as follows:

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

When the driver is loaded, you can establish a connection by using a connection URL and the getConnection method of the DriverManager class:

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
"databaseName=AdventureWorks;user=MyUserName;password=*****;";
Connection con = DriverManager.getConnection(connectionUrl);
In the JDBC API 4.0, the DriverManager.getConnection method is enhanced to load JDBC drivers automatically. Therefore, applications do not need to call the Class.forName method to register or load the driver when using the sqljdbc4.jar class library.

When the getConnection method of the DriverManager class is called, an appropriate driver is located from the set of registered JDBC drivers. sqljdbc4.jar file includes "META-INF/services/java.sql.Driver" file, which contains the com.microsoft.sqlserver.jdbc.SQLServerDriver as a registered driver. The existing applications, which currently load the drivers by using the Class.forName method, will continue to work without modification.

Note:
sqljdbc4.jar class library requires a Java Runtime Environment (JRE) of version 6.0 or later.

Recover or reset QSECOFR passwords

When IBM ships a server, both a QSECOFR OS/400 user profile and a QSECOFR service tools user ID are supplied. These are not the same. They exist in different locations and are used to access different functions. Your QSECOFR service tools user ID can have a different password from your QSECOFR OS/400 user profile. Service tools user IDs have different password policies than OS/400 user profiles.
If you lose or forget the passwords for both the QSECOFR OS/400 user profile and the QSECOFR service tools user ID, you may need to install your operating system again to recover them. Contact your service provider for assistance. If you know either of these passwords, this information tells you how to recover the password you do not know.
Reset the QSECOFR OS/400 user profile password
If you know the QSECOFR service tools user ID, you can use it to reset the QSECOFR OS/400 user profile to its initial value (QSECOFR). This procedure requires you to perform an initial program load (IPL) on your server. The change does not take affect until after the IPL. Complete the following steps to reset the QSECOFR OS/400 user profile:
Start DST.
Enter the QSECOFR service tools user ID and password on the DST Sign-On display.
Select option 5 (Work with DST environment) from the Use DST menu.
Select option 6 (Work with Service Tools Security Data) from the Work with DST Environment menu. You will see the Work with Service Tools Security Data menu:
+——————————————————————————–+
| Work with Service Tools Security Data |
| System: _____________ |
| Select one of the following: |
| 1. Reset operating system default password |
| 2. Change operating system install security |
| 3. Work with service tools security log |
| 4. Restore service tools security data |
| 5. Save service tools security data |
| 6. Password level |
| Selection |
+——————————————————————————–+
Select option 1 (Reset operating system default password). The Confirm Reset of System Default Password display appears.
Press Enter to confirm the reset. A confirmation message appears telling you that the system has set the operating system password override.
Continue pressing F3 (Exit) to return to the Exit DST menu.
Select option 1 (Exit DST). The IPL or Install the System menu appears.
Select option 1 (Perform an IPL). The system continues with a manual IPL. If you need additional information about performing an IPL, see the Starting and stopping the iSeries topic.
When the IPL completes, return the keylock switch or electronic keystick to the Auto position, if applicable.
Sign on to OS/400 as QSECOFR. Use the CHGPWD command to change the QSECOFR password to a new value. Store the new value in a safe place.
Attention: Do not leave the QSECOFR password set to the default. This is a security exposure because this is the value shipped with every iSeries server and is commonly known.
Reset the QSECOFR service tools user ID and password
If you know the password for the QSECOFR OS/400 user profile, you can use it to reset the password for the IBM-supplied service tools user ID that has service tools security privilege (QSECOFR) to the IBM-supplied default value by completing the following steps:
Ensure that the server is in normal operating mode, not DST.
Sign on at a workstation using the QSECOFR OS/400 user profile.
On a command line, type CHGDSTPWD (Change IBM Service Tools Password). You see the Change IBM Service Tools Password (CHGDSTPWD) display:
+——————————————————————————–+
| Change IBM Service Tools Pwd (CHGDSTPWD) |
| |
|Type choices, press Enter. |
| |
|Password . . . . . . . . . . . . *DEFAULT *SAME, *DEFAULT |
| |
+——————————————————————————–+
Type *DEFAULT and press the Enter key. This sets the IBM-supplied service tools user ID that has service tools security privilege and its password to QSECOFR.
Attention: Do not leave the QSECOFR service tools user ID and password set to the default value. This is a security exposure because this is the value shipped with every iSeries server and is commonly known. See the Recommendations for managing service tools user IDs for more information.

Parsing An Excel Spreadsheet with as400 RPG and Java

Scott Klement has an article on how to do this on the as400 here...

In the May 15, 2003, issue of Club Tech iSeries Programming Tips, some information was presented on how to use the Jakarta-POI/HSSF Java classes together with an iseries RPG program to create an Excel spreadsheet. In this tip, the same open-source Java classes will be used to do the opposite: to extract data from an existing Excel spreadsheet.

http://www.easy400.net/hssfcgi/documentation/17840.html

The downloadable as400 source code for this tip contains an RPG service program and Java .jar file that work together with the HSSF classes to make parsing an Excel document a snap. All you have to do is provide a as400 rpg subprocedure for character cells and a subprocedure for numeric cells, and the service program will call them back for each cell in the workbook

Unleash the power of i5/os on your windows server using the QNTC File System

Once you have QNTC configured, and you have a user profile that can perform operations on your target Windows file shares, you can use QNTC to read and write stream file data to a Windows server in the same way you would normally use the root (/) directory of the IFS, the QDLS file system, or the OpenSys IFS file systems in application programs. Your Windows file systems merely become targets for ASCII and stream file operations, which gives you the ability to use your Windows servers to read and update Windows data in OS/400 programs or to back up Windows data to an OS/400-based tape drive by using the Save Object (SAV) command

http://www.itjungle.com/fhg/fhg031704-story04.html

Using Reserved Words in as400 sql queries

If your trying to run an SQL statement on the as400, but are getting errors because one of your column names is an DB2/400 SQL reserved word, try the following.

Wrap double quotes around the offending column name, and also make it uppercase. This should resolve the reserved words problem!

Locking down and logging iSeries file access from SQL & FTP commands from windows/unix/java environment

Each server application on the as400, first calls an exit program, if one exists, and then continues to access the data. Eg The exit program for ODBC is QIBM_QZDA_INIT.

The steps to creating an exit program are

Create you exit program

Use WRKREGINF to work with exit programs
Use option 1 to add the program to the list of registered exit programs

This link to an example:

http://publib.boulder.ibm.com/infoce...imstsmprpg.htm

Examples: Create exit programs with RPG

The following example illustrates how to set up a user exit program with RPG*.

Note: Read the Code example disclaimer for important legal information.

 

    **
      ** OS/400 SERVERS - SAMPLE USER EXIT PROGRAM
      **
      ** THE FOLLOWING RPG PROGRAM UNCONDITIONALLY
      ** ACCEPTS ALL REQUESTS. IT CAN BE USED AS A SHELL
      ** FOR SPECIFIC APPLICATIONS. NOTE: REMOVE THE
      ** SUBROUTINES AND CASE STATEMENT ENTRIES FOR THE SERVERS
      ** THAT DO NOT REQUIRE
      ** SPECIFIC EXIT PROGRAM HANDLING FOR BETTER PERFORMANCE.
      **
     E*
     E* NECESSARY ARRAY DEFINITIONS FOR TRANSFER FUNCTION
     E* AND REMOTE SQL
     E*
     E                    TFREQ    4096  1
     E                    RSREQ    4107  1
     I*
     I*
     IPCSDTA      DS
     I                                        1  10 USERID
     I                                       11  20 APPLID
     I*
     I* SPECIFIC PARAMETERS FOR VIRTUAL PRINTER
     I*
     I                                       21  30 VPFUNC
     I                                       31  40 VPOBJ
     I                                       41  50 VPLIB
     I                                       71  750VPIFN
     I                                       76  85 VPOUTQ
     I                                       86  95 VPQLIB
     I*
     I* SPECIFIC PARAMETERS FOR MESSAGING FUNCTION
     I                                       21  30 MFFUNC
     I*
     I* SPECIFIC PARAMETERS FOR TRANSFER FUNCTION
     I*
     I                                       21  30 TFFUNC
     I                                       31  40 TFOBJ
     I                                       41  50 TFLIB
     I                                       51  60 TFMBR
     I                                       61  70 TFFMT
     I                                       71  750TFLEN
     I                                       764171 TFREQ
     I*
     I* SPECIFIC PARAMETERS FOR FILE SERVER
     I*
     I* NOTE: FSNAME MAY BE UP TO l6MB.
     I* FSNLEN WILL CONTAIN THE ACTUAL SIZE OF FSNAME.
     I*
     I                                    B  21  240FSFID
     I                                       25  32 FSFMT
     I                                       33  33 FSREAD
     I                                       34  34 FSWRIT
     I                                       35  35 FSRDWR
     I                                       36  36 FSDLT
     I                                    B  37  400FSNLEN
     I                                       41 296 FSNAME
     I*
     I* SPECIFIC PARAMETERS FOR DATA QUEUES
     I*
     I                                       21  30 DQFUNC
     I                                       31  40 DQQ
     I                                       41  50 DQLIB
     I                                       70  750DQLEN
     I                                       76  77 DQROP
     I                                       78  820DQKLEN
     I                                       83 338 DQKEY
     I*
     I* SPECIFIC PARAMETERS FOR REMOTE SQL
     I*
     I                                       21  30 RSFUNC
     I                                       31  40 RSOBJ
     I                                       41  50 RSLIB
     I                                       51  51 RSCMT
     I                                       52  52 RSMODE
     I                                       53  53 RSCID
     I                                       54  71 RSSTN
     I                                       72  75 RSRSV
     I                                       764182 RSREQ
     I*
     I* SPECIFIC PARAMETERS FOR NETWORK PRINT SERVER
     I*
     I                                       21  28 NPFT
     I                                    B  29  320NPFID
     I* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT SPLF0l00
     I                                       33  42 NPJOBN
     I                                       43  52 NPUSRN
     I                                       53  58 NPJOB#
     I                                       59  68 NPFILE
     I                                    B  69  720NPFIL#
     I                                    B  73  760NPLEN
     I                                       77 332 NPDATA
     I*
     I* Data queue server:
     I*
     I* QIBM_QZHQ_DATA_QUEUE  format ZHQ00100
     I*
     I                                       21  28 DQOFMT
     I                                    B  29  320DQOFID
     I                                       33  42 DQOOBJ
     I                                       43  52 DQOLIB
     I                                       53  54 DQOROP
     I                                    B  55  580DQOLEN
     I                                       59 314 DQOKEY
     I*
     I* Specific PARAMETERS FOR CENTRAL SERVER
     I*
     I                                       21  28 CSFMT
     I                                    B  29  320CSFID
     I* Central server:
     I*
     I* QIBM_QZSC_LM format ZSCL0l00 for license management calls
     I*
     I*
     I                                       33 287 CSLCNM
     I                                      288 295 CSLUSR
     I                                      296 302 CSLPID
     I                                      303 306 CSLFID
     I                                      307 312 CSLRID
     I                                    B 313 3140CSLTYP
     I*
     I* Central server:
     I*
     I* QIBM_QZSC_LM format ZSCS0l00 for system management calls
     I*
     I*
     I                                       33 287 CSSCNM
     I                                      288 542 CSSCMY
     I                                      543 543 CSSNDE
     I                                      544 798 CSSNNM
     I*
 
     I* Central server:
     I*
     I* QIBM_QZSC_LM format ZSCN0l00 for retrive conversion map calls
     I*
     I*
     I                                       21  30 CSNXFM
     I                                       29  320CSNFNC
     I                                    B  33  360CSNFRM
     I                                    B  37  400CSNTO
     I                                    B  41  420CSNCNT
     I*
     I* SPEClFIC PARAMETERS FOR DATABASE SERVER
     I*
     I                                       21  28 DBFMT
     I                                    B  29  320DBFID
     I*
     I* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAD0l00
     I                                       33 160 DBDFIL
     I                                      161 170 DBDLIB
     I                                      171 180 DBDMBR
     I                                      181 190 DBDAUT
     I                                      191 318 DBDBFL
     I                                      319 328 DBDBLB
     I                                      329 338 DBDOFL
     I                                      339 348 DBDOLB
     I                                      349 358 DBDOMB
     I*
     I* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAD0200
     I                                    B  33  360DBNUM
     I                                       37  46 DBLIB2
     I*
     I* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAQ0l00
     I                                       33  50 DBSTMT
     I                                       51  68 DBCRSR
     I                                       69  70 DBOPI
     I                                       71  72 DBATTR
     I                                       73  82 DBPKG
     I                                       83  92 DBPLIB
     I                                    B  93  940DBDRDA
     I                                       95  95 DBCMT
     I                                       96 351 DBTEXT
     I* THE FOLLOWING PARAMETERS REPLACE DBTEXT FOR FORMAT ZDAQ0200
     I                                       96 105 DBSQCL
     I                                    B 133 1360DBSQLN
     I                                      137 392 DBSQTX
     I* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAR0l00
     I                                       33  52 DBLIBR
     I                                       53  88 DBRDBN
     I                                       89 108 DBPKGR
     I                                      109 364 DBFILR
     I                                      365 384 DBMBRR
     I                                      385 404 DBFFT
 
     I* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAR0200
     I                                       33  42 DBRPLB
     I                                       43 170 DBRPTB
     I                                      171 180 DBRFLB
     I                                      181 308 DBRFTB 
     I*
     I* Remote command and distributed program call server:
     I*
     I* QIBM_QZRC_RMT  format CZRC0100
     I*    RCPGM AND RCLIB ARE NOT USED FOR REMOTE COMMAND CALLS
     I*
     I                                       21  28 RCFMT
     I                                    B  29  320RCFID
     I                                       33  42 RCPGM
     I                                       43  52 RCLIB
     I                                    B  53  560RCNUM
     I                                       57 312 RCDATA
     I*
     I* signon server:
     I*
     I* QIBM_QZSO_SIGNONSRV format ZSOY0l00 for TCP/IP signon server
     I*
     I                                       21  28 SOXFMT
     I                                    B  29  320SOFID
     I*
     I***************************************************************
     I*
     I              '*VPRT     '          C         #VPRT
     I              '*TFRFCL   '          C         #TRFCL
     I              '*FILESRV  '          C         #FILE
     I              '*MSGFCL   '          C         #MSGF
     I              '*DQSRV    '          C         #DQSRV
     I              '*RQSRV    '          C         #RQSRV
     I              '*SQL      '          C         #SQL
     I              '*NDB      '          C         #NDBSV
     I              '*SQLSRV   '          C         #SQLSV
     I              '*RTVOBJINF'          C         #RTVOB
     I              '*DATAQSRV '          C         #DATAQ
     I              'QNPSERVR  '          C         #QNPSV
     I              '*CNTRLSRV '          C         #CNTRL
     I              '*RMTSRV   '          C         #RMTSV
     I              '*SIGNON   '          C         #SIGN
     I*
     C*
     C* EXIT PROGRAM CALL PARAMETERS
     C*
     C           *ENTRY    PLIST
     C                     PARM           RTNCD   1
     C                     PARM           PCSDTA
     C*
 
     C* INITIALIZE RETURN VALUE TO ACCEPT REQUEST
     C*
     C                     MOVE '1'       RTNCD
     C*
     C* COMMON PROCESSING
     C*
     C*              COMMON LOGIC GOES HERE
     C*
     C* PROCESS BASED ON SERVER ID
     C*
     C           APPLID    CASEQ#VPRT     VPRT
     C           APPLID    CASEQ#TRFCL    TFR
     C           APPLID    CASEQ#FILE     FILE
     C           APPLID    CASEQ#MSGF     MSG
     C           APPLID    CASEQ#DQSRV    DATAQ
     C           APPLID    CASEQ#RQSRV    RSQL
     C           APPLID    CASEQ#SQL      SQLINT
     C           APPLID    CASEQ#NDBSV    NDB
     C           APPLID    CASEQ#SQLSV    SQLSRV
     C           APPLID    CASEQ#RTVOB    RTVOBJ
     C           APPLID    CASEQ#DATAQ    ODATAQ
     C           APPLID    CASEQ#QNPSV    NETPRT
     C           APPLID    CASEQ#CNTRL    CENTRL
     C           APPLID    CASEQ#RMTSV    RMTCMD
     C           APPLID    CASEQ#SIGN     SIGNON
     C                     END
     C                     SETON                     LR
     C                     RETRN
     C*
     C* SUBROUTINES
     C*
     C*
     C* VIRTUAL PRINT
     C*
     C           VPRT      BEGSR
     C*            SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* TRANSFER FUNCTION
     C*
     C* THE FOLLOWING IS AN EXAMPLE OF SPECIFIC PROCESSING
     C* THAT THE EXIT PROGRAM COULD DO FOR TRANSFER FUNCTION.
     C*
 
     C* IN THIS CASE, USERS ARE NOT ALLOWED TO SELECT
     C* DATA FROM ANY FILES THAT ARE IN LIBRARY QIWS.
     C*
     C           TFR       BEGSR
     C           TFFUNC    IFEQ 'SELECT'
     C           TFLIB     ANDEQ'QIWS'
     C                     MOVE '0'       RTNCD
     C                     END
     C                     ENDSR
     C*
     C*
     C* FILE SERVER
     C*
     C           FILE      BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* MESSAGING FUNCTION
     C*
     C           MSG       BEGSR
     C*             SPECIFIC LOGIC GOFS HERE
     C                     ENDSR
     C* DATA QUEUES
     C*
     C           DATAQ     BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* REMOTE SQL
     C*
     C           RSQL      BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* SERVERS
     C*
     C*
     C* DATABASE INIT
     C*
     C           SQLINT    BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* DATABASE NDB (NATIVE DATABASE)
     C*
     C           NDB       BEGSR
     C*             SFECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
 
     C* DATABASE SQL
     C*
     C           SQLSRV    BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* DATABASE RETRIEVE OBJECT INFORMATION
     C*
     C           RTVOBJ    BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* DATA QUEUE SERVER
     C*
     C           ODATAQ    BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* NETWORK PRINT
     C*
     C           NETPRT    BEGSR
     C*             SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
     C* CENTRAL SERVER
     C*
     C*
     C* THE FOLLOWING IS AN EXAMPLE OF SPECIFIC PROCESSING
     C* THAT THE EXIT PROGRAM COULD DO FOR LICENSE MANAGEMENT.
     C*
     C* IN THIS CASE, THE USER "USERALL" WILL NOT BE ALLOWED
     C* TO EXECUTE ANY FUNCTIONS THAT ARE PROVIDED BY THE
     C* CENTRAL SERVER FOR WHICH THIS PROGRAM IS A REGISTERED
     C* EXIT PROGRAM - LICENSE INFORMATION, SYSTEM MANAGEMENT
     C* OR RETRIVE A CONVERSION MAP.
     C*
     C           CENTRL    BEGSR
     C           USERID    IFEQ 'USERALL'
     C                     MOVE '0'       RTNCD
     C                     ENDIF
     C*            SPECIFIC LOGIC GOES HERE
     C                     ENDSR
     C*
 
     C* REMOTE COMMAND AND DISTRIBUTED PROGRAM CALL
     C*
     C* IN THIS CASE, THE USER "USERALL" WILL NOT BE ALLOWED
     C* TO EXECUTE ANY REMOTE COMMANDS OR REMOTE PROGRAM CALLS
     C*
     C           RMTCMD    BEGSR
     C           USERID    IFEQ 'USERALL'
     C                     MOVE '0'       RTNCD
     C                     ENDIF
     C                     ENDSR
     C*
     C* SIGNON SERVER
     C*
     C           SIGNON    BEGSR
     C*                  SPECIFIC LOGIC GOES HERE
     C                     ENDSR

Automating a process to run every 10 minutes

If you need to run a as400 job, say a database query or report on a regular schedule, heres and example of how to do this. Lets say you needed to run a query every 10 mins...

You could submit a CL program, MYJOB to do this, to batch, with a 10 minute delay and a data area to terminate the job externally...


MYJOB:
Loop:
Run database query
Print Report
FTP data
etc

dlyjob 10 minutes
check kill dataarea and exit if set
goto loop

The problem here is that the job will could run for almost 10 minutes after, if the kill data area is set just after its checked. If you operator needs to shut down the system in a hurry you dont want him waiting an extra 10 minutes for a DLYJOB.

A better way to do the is to replace the read of a data area with a read of a data queue. The CL can then wait the required time for a kill entry to appear on the data queue. If it does you end processing runs immediately, if not you core processing runs again, then the check again. The most you will have to wait is the length of time it takes to run your core processing! So no DLYJOB required, just a QRCVDTAQ (and a QSNDDTAQ to set it of course)


MYJOB:
Loop:
Run database query
Print Report
FTP data
etc

check and wait on kill dataqueue for 10 mins and exit if set
goto loop

Using free-form RPG program to call another RPG program

If you get RNF3751 'External procedure on prototype for main procedure is not the same as actual external name' when trying to call an RPG using free format, check you procedure interface. Your PR and your PI need to match!

eg

For program myrpg01

d myrpg01 pr
d parm 1A

d myrpg01 pi
d parm 1A

then to call

either CALLP myrpg01('MYPARM');

or just

myrpg01('MYPARM');

Trying to find out as400 library size

To find out your as400 library sizes run

DSPLIB LIB(*All) OUTPUT(*PRINT)

in a batch job.

This will show you the size of all objects in the as400 libraries and the total library size including the objects at the bottom of each library in the report.

Creating an AS/400 passthrough query in MS Access

Passthrough queries can be efficient for AS/400 ODBC connections because they let you bypass the JET engine and take advantage of the AS/400's query processor.

To create a passthrough query in Access, open the Queries windows and then double-click the "Create query in design view" icon. Close the New Tables dialog box that automatically appears. Then, on the Query menu, select SQL Specific and then Pass-Through option. Next, set the ODBC connection string by selecting the Properties icon in the toolbar and then clicking on the ellipsis next to the ODBC Connect Str property. You're then prompted to select an existing data source to the AS/400, or you can create a new data source if an AS/400 data source doesn't already exist. After setting the connection properties, type your passthrough query in the SQL Pass-Through Query window and click the Execute icon to run the query

Typical settings for MS OLE DB Provider for IBM DB2

Example settings for configuring MS OLE DB provider for DB2 for as400 iseries

Affiliate Application
Alternate TP Name
APPC Local LU Alias
APPC Mode Name              QPCSUPP
APPC Remote LU Alias
APPC Security Type          Program
Auth Encrypt    False
Cache Authentication        False
Client Application Name 
Connection Pooling          False
Data Source                 myas400.mydomain.com
DateTime As Char            False
DateTime as Date            False
DBMS Platform               DB2/AS400
Default Qualifier
Default Schema              MYDATA (note, this is the library name containing the files to access on as400)
Defer Prepare               False
Derive Parameters           False
Extended Properties  
Host CCSID                  37
Initial Catalog             MY400
Integrated Security
Mode                        ReadWrite
Network Address             myipaddress
Network Port                446
Network Transport Library   TCPIP
New Password
Package Collection          SQLPKG (library to contain the packages created for pulling/pushing data from/to this box)
PC Code Page                1252
Persist Security Info       False
Principle Name
Process Binary as Character false
Rowset Cache Size           0
Units of Work               RUW
Use Early Metadata          False
Locale Identifier           1033
Mode                        3
Protection Level            1
Connection Pooling          false

More as400 settings to follow