PC, ODBC & AS/400

What you find in this page.

Here are some answers for Microsoft Access/Visual Basic programmers, which are working with the AS/400 system, Client Access and ODBC. There are many AS/400, ODBC and Client Access information sources, but not together. I need these answers for my work and to find them was not simple for me.

I know my English is far from perfect.

You can also visit my personal homepage.

Updated 10/Jan/00
New: Updated:

1. Basic info

1.1 Where are other resources ?

1.2 Which software for AS connectivity is available ?

Search the iSeries DocFinder for the words Buyers Guide. Most articles are paid, see 1.1.

2. SQL

2.1 Where is some SQL tutorial ?

On Introduction to SQL page and you can buy the SQL/400 Developer's Guide book.

2.2 What are SQL/400 advantages ?

Speed and good documentation. See also DB2 Advanced Database Functions.

2.3 What means all the DB2 and SQL terms ?

See DB2 FAQ and SQL FAQ, parts of Data Processing FAQ. There is also a IBM Glossary of computing terms, it has even a special AS/400 part.

2.4 AS/400 logical file is SQL Index or View ?

Citation: "Both keyed and non-keyed logical files are effectively the same as a VIEW, and a keyed logical file *also* can provide effectively the same effect as an INDEX .

3. Microsoft Access

3.1 Where are other resources ?

MS Access FAQ sites 1, and 2, MS office developer forum, MS technical support and links from the MS Access Help Menu.

3.2 What is DAO, RDO, ODBC, OLE DB, ADO ?

See Microsoft article and OLE DB FAQ.

3.3 How to call Client Access API, Windows and DLL functions ?

Declarations of these functions are usually in C and Windows API. See Using API in VB. There is also great Windows API Guide and Visual Basic developer site.

3.4 How to attach a table in a program ?

Sub ConnectExample()
ConnectTable "MyTable", "MYLIB.MYFILE", "AS400Driver"
End Sub

Sub ConnectTable(strTableName As String, strAS400File As String, strODBCDriver As String)
Dim Db As Database, T As TableDef
Set Db = CurrentDb
Set T = Db.CreateTableDef(strTableName)
T.Connect = "ODBC;DSN=" & strODBCDriver & ";DATABASE="
T.SourceTableName = strAS400File
Db.TableDefs.Append T
End Sub

3.5 Why is attached table read-only ?

Because it has no primary key. ODBC is based on SQL and SQL needs unique record identification. It is possible to manipulate with them with SQL insert/delete/update commands only.

3.6 How to set member in attached table ?

You must first create alias and connect the alias instead of physical file, see 4.8 for ALIAS description.

4. ODBC

4.1 How to connect to AS ?

Public wspAS400 As Workspace, cntAS400 As Connection, dbAS400 As Database

Sub ConnectToAS400()
Set wspAS400 = DBEngine.CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
Set dbAS400 = wspAS400.OpenDatabase("TemporaryName", , , "ODBC;DSN=" & strODBC & ";DATABASE=")
Set cntAS400 = wspAS400.Connections(0)
End Sub

In MS Access never attach wspAS400 into Workspaces collection.

4.2 Which AS job serves ODBC and how to manipulate with it ?

QZDASOINIT in QSERVER subsystem. You can find it with WRKACTJOB, if there are several such jobs, see the joblogs. It's possible to work with it in two ways: For info about its library list see the IBM Article.

4.3 How AS serves ODBC SQL commands ?

In the same way as it serves all SQL commands, here is excerpt from the book:

TermMeaning
TableData repository. Created through CRTPF or CREATE TABLE.
Data spaceAnother term for data repository.
IndexBinary tree built over table to order particular columns (keys) of the table and for quick binary searches. Created through CRTLF or CREATE INDEX.
Temporary indexIndex built 'on the fly' by the optimizer.
Temporary resultCopy of data from an intermediate query step. Needed to complete the query.
Access planPlan generated by optimizer on how to access the tables being queried.
ODPOpen Data Path. Active path through which query data is read.
Reusable ODPODP kept open when an SQL query is requested to be closed. Used next time query is opened.

Before an application program with SQL statements is run, a relationship between the program/SQL statement and the referenced tables, views, and indexes must be established. This process is referred to as binding. The result of a successful bind is an access plan. This access plan is created during program creation for static SQL or during statement preparation for dynamic SQL. It contains internal structures and information about the access methods used to run a specific SQL statement. An access plan may become invalid if changes to the database are detected. An example of a change is creating or deleting an index for a referenced table.

The access plan is a control structure and information that specifies how to run each SQL request in the most efficient manner. The access plan may be stored in the program (non-distributed SQL) or in an SQL package (distributed SQL).

The process of binding:

  • Validates the SQL statements using database description.
  • Selects the required indexes.
  • Builds the access plan.

4.4 How SQL packages improve ODBC performance ?

See the article. In a SQL package aren't stored all SQL commands, only a parameterized commands(4.5).

4.5 What is and how to use a parameterized SQL command ?

It is the fastest way for seeking record in a big table.

Dim qryMyQuery As QueryDef, rstRecs as Recordset
Set qryMyQuery = cntAS400.CreateQueryDef("", "SELECT * FROM MYLIB.MYTABLE WHERE YYY=? AND ZZZ=?")
qryMyQuery(0) = ValueForYYY
qryMyQuery(1) = ValueForZZZ
Set rstRecs = qryMyQuery.OpenRecordSet
If (rstRECS.EOF = False ) Then
... working with found record(s)
End If
qryMyQuery(0) = SomeOtherValueForYYY
qryMyQuery(1) = SomeOtherValueForZZZ
Set rstRecs = qryMyQuery.OpenRecordSet
If (rstRECS.EOF = False ) Then
... working with found record(s)
End If
...

It is the only way how to insert rows into non-unique keyed table (3.5):

Dim qryMyQuery As QueryDef
Set qryMyQuery = cntAS400.CreateQueryDef("", "INSERT INTO MYLIB.MYTABLE (MYFIELD1,MYFIELD2) VALUES (?,?)")
qryMyQuery(0) = ValueForMYFIELD1
qryMyQuery(1) = ValueForMYFIELD2
qryMyQuery.Execute

Parameterized UPDATEs and DELETEs are possible too, of course.

4.6 What to do with ODBC errors ?

4.6.1 Error QSQL0666 Estimated query processing time &1 exceeds limit &2

ODBC Querydef has ODBCTimeout property. AS estimates the time and if it is larger, query is not executed and error is reported. Default is 60 seconds. Try optimize the query (4.11).

4.6.2 Error Data truncated

You are storing into AS field a number with more decimal positions, than it allows. It will happen often, if you use Single/Double data types, which have decimal numbers stored always (in principle) inaccurately. For example 4.2-0.3 and 3.8+0.1 are not equal in MS Access. The result of calculation can be for example 12.3000001 instead of 12.3. In PC are inaccurate numbers and (in addition) AS transfer fails. Use Currency data type, which has four decimals only, but accurate. Single/Double data are evil.

4.6.3 Communication link failure, COMM RC=0xnn

This is low-level error, see IBM Technical support.

4.7 How to execute remote command through ODBC ?

Sub ExecuteCmdExample()
ExecuteAS400Cmd "CLRPFM MYLIB/MYFILE"
End Sub

Sub ExecuteAS400Cmd(strCmd As String)
dbAS400.Execute ("CALL QSYS.QCMDEXC('" & strCmd & "', " & Format(Len(strCmd), "0000000000") & ".00000)")
End Sub

If the called program has numeric parameters, the parameters often must be precisely formatted, see 6.2.

4.8 How to work with members ?

Sub MemberExample()
SetMember "MYFILE", "MYMEMBER"
Dim R As Recordset
Set R = dbAS400.OpenRecordset("SELECT * FROM MYFILE")
...
End Sub

Sub SetMember(strFile As String, strMember As String)
ExecuteAS400Cmd "OVRDBF FILE(" & strFile & ") TOFILE(" & strFile & ") MBR(" & strMember & ") SECURE(*YES) OVRSCOPE(*JOB) OPNSCOPE(*JOB) SHARE(*YES)"
End Sub

Effect of OVRDBF command is temporary; if you log off, it disappears. From DB2 V4R3 it's possible to create aliases, which are persistent:
dbAS400.Execute ("CREATE ALIAS MYLIB.MYFILEMBR1 FOR MYLIB.MYFILE (MBR1)")

Alias exists until is dropped.

4.9 Which is name of connected user ?

Public Declare Function cwbSY_CreateSecurityObj Lib "cwbsy.dll" (ByRef lngSecurity As Long) As Long
Public Declare Function cwbSY_DeleteSecurityObj Lib "cwbsy.dll" ( ByVal lngSecurityHandle As Long) As Long
Public Declare Function cwbSY_GetUserID Lib "cwbsy.dll" ( ByVal lngHandle As Long, ByVal strSystemName As String, ByVal strUserId As String) As Long

Function strGetUserId(strAS400Name As String) As String
Dim lngSecurityHandle As Long, lngResult As Long, strUserId As String
strUserId = Space(11)
lngResult = cwbSY_CreateSecurityObj(lngSecurityHandle)
lngResult = cwbSY_GetUserID(lngSecurityHandle, strSystemName, strUserId)
If (lngResult = 0) Then strGetUserId = Mid(strUserId, 1, InStr(1, strUserId, Chr(0)) - 1)
lngResult = cwbSY_DeleteSecurityObj(lngSecurityHandle)
End Function

4.10 How is the comparison rule for strings ?

The setting in Language panel of ODBC driver.

4.11 How to optimize ODBC performance ?

In the debug mode, the process and results of SQL optimization are stored into the joblog (4.2).

ExecuteAS400Cmd "STRDBG UPDPROD(*YES)"
Dim rstRecs as Recordset
Set rstRecs = dbAS400.OpenRecordSet("My complicated query")
... now the information is in the joblog.
ExecuteAS400Cmd "ENDDBG"

Optimization is very easy.
For more see the book .

4.12 How to get info about tables, fields, indexes, records ... ?

From catalog files.

4.13 How to insert more rows at once (blocked insert) ?

As I know, it's not possible with Visual Basic QueryDef object. You must call directly ODBC API, there is example in C.

5. Miscellaneous

5.1 How to transfer AS objects using my PC mail ?

Storing objects into the PC file:

  1. Create a savfile on AS and store the objects into it:
    CRTSAVF MYLIB/MYSAVFILE
    SAVOBJ ...
  2. On PC start "FTP AS_MACHINE_NAME", it prompts for name+password
  3. ftp> binary
  4. ftp> get MYLIB/MYSAVFILE C:\...\MYSAVFILE.SAV
  5. ftp> quit
Now in the PC is MYSAVFILE.SAV file, which contains the objects.

Restoring AS objects from PC file:

  1. Create a savfile on AS .
    CRTSAVF MYLIB/MYSAVFILE
  2. On PC start "FTP AS_MACHINE_NAME", it prompts for name+password
  3. ftp> binary
  4. ftp> put C:\...\MYSAVFILE.SAV MYLIB/MYSAVFILE
  5. ftp> quit
  6. On AS use RSTOBJ.
There is also AS/400 FTP FAQ.

5.2 IBM Library reader for Windows reports EJRBK066 error.

See the readme.txt. If you have Windows 95, the advice is "use CHANGECP command, which is only on original Windows 95 CD ROM", but this can be done by hand, if you set "oemansi.bin=" in system.ini file and restart Windows. After this, a national support in DOS will be unavailable.

5.3 What should I be aware of with database design ?

There are simple rules.

5.4 How to run PC command from AS ?

With Win 95/NT Client Access CWBRXD.EXE utility. See Start->Programs->IBM AS400 Client Access->Client Access User's Guide (ODUGDE.HLP)->How To->Incoming Remote Command.

5.5 How to search AS source files ?

There is FNDSTRPDM command.

6. AS/400 specific

6.1 What are the file members for ?

It depends on you. If you are sure you don't want see the data from multiple members of one file at once, you can use them to simplify the work.

For example if you create invoice database, you can store each month in one member.
Advantages:

Nevertheless every time you ask: "Why should I use members, when there are other ways to do it", you can also ask: "Why should I use other ways, when I have members ?".

Also the members are used for source files. This is reasonable, because AS has no directory hierarchy.

6.2 How to pass number parameters to a AS program ?

By default, the number is converted to packed number as it was declared
DCL &Number *DEC (15 5)
If you declare it for example
DCL &Number *DEC (3 0)
you must pass it in hexadecimal, for example number 15
CALL PROG1 PARM(X'015F')
There is also the article.

6.3 How to import data from heterogenous databases ?

See info.
Counter: Counter, page stats: stats.

Sign Guestbook
View Guestbook
Last updated: 28/Feb/01
1