Choose As400 Query Records Directly from Excel

AS400 and System I Navigator

First a little terminology, AS/400 is an old term, the current name for the Platform and OS that used to be called AS/400 is now IBM i on Power Systems. IBM i is the OS. (that is until IBM changes the name again)

If You Know the Table Name but not the IBM i Object Name

On IBM i, the database is built into the OS and many of the OS objects are in fact database objects. Here is how some of the SQL concepts map to IBM i terms.

SQL              IBM i
--------------   ------------------
Schema           Library
Table            Physical file
Index            Logical file
View             Logical file
Row              Record
Column           Field

Unfortunately in IBM i, object names are limited to 10 characters. SQL names on the other hand can be up to 128 characters. You won't find a Physical file named CustomerMaster. DB2 maps that long name to a system name. You can find the system name by querying the catalog like this:

select system_schema_name, system_table_name
  from qsys2.systables
  where table_name = 'Navigator name'

The column TABLE_NAME will hold the long SQL name of the table, SYSTEM_TABLE_NAME will hold the IBM i object name. Note that long schema names can be mapped to system names as well. The column SCHEMA_NAME holds the long SQL name of the schema while SYSTEM_SCHEMA_NAME holds the IBM i library name. It is uncommon for schema names to be longer than 10 characters, so the two schema name columns are typically the same.

If You Know the Program Name, and Have Access to the Source

This may be obvious to you, but I am putting it here just for completeness. You can look in the source for the files being used, and back track from the screen field to the file.

If You Only Have A Green Screen

You can retrieve the open files for the current job if you have the appropriate authority. If this doesn't work for you, you will have to get help from your system administrator, or someone who does have authority. This will only get you candidate files though, and likely they are logical files. To do this, you are going to have to have authority to view your job, and you will have to know how the system request key is mapped to your keyboard (that is implementation specific, and may be customized, so you will have to check with someone inside your company or your emulator to determine that).

With that behind us, start the green screen program that shows the price field you are looking for. Then press the system request key. If you are configured to allow this, you will get an input line on the bottom of your screen, and the cursor will be positioned to it.

Press Enter.

You should now be in the System Request menu.

Select option 3 and press enter again. You should be in the Display Job screen for your current job.

If this all worked correctly for you, then option 12 will show you the files that your job currently has a lock on. That is, the files that are open for your job. The price field should be in one of them.

Possible to query As400 DB2 via VB.Net without a PRG?

Here is a simple console app that will retrieve all the records from one table and display the row count.

Imports System.Data.OleDb

Module Module1

  Sub Main()
    Dim cmd As New OleDbCommand
    Dim table As String = "YOUR TABLE"
    cmd.CommandText = "SELECT * FROM " & table
    Dim ip As String = "YOUR AS/400 IP GOES HERE"
    Dim user As String = "YOUR USER ID"
    Dim pass As String = "YOUR PASSWORD"
    Dim defaultLib As String = "YOUR LIBRARY"
    Dim connstring As String = "Provider=IBMDA400;" & _
                               "Data Source=" & ip & ";" & _
                               "Force Translate=0;" & _
                               "Default Collection=" & defaultLib & ";" & _
                               "User ID=" & user & ";" & _
                               "Password=" & pass
    cmd.Connection = New OleDbConnection(connstring)
    cmd.Connection.Open()
    Dim dr As OleDbDataReader = cmd.ExecuteReader
    Dim dt As New DataTable
    dt.Load(dr)

    Console.WriteLine(dt.Rows.Count)
    Console.WriteLine("Press ENTER to close...")
    Console.ReadLine()
  End Sub

End Module

AS400 Query/Excel ODBC Union Error

AJITEM and ILITEM are VARCHAR(20). I was comparing it to an INT. Fixed and the code below works with zero issues. My fault for not stating this in the original question and overlooking such a simple mistake.

SELECT ADJTRAN.AJAISL AS AISLE, ADJTRAN.AJSLOT AS SLOT
FROM S216F06V.WDLSDATA.ADJTRAN ADJTRAN
WHERE (AJITEM='8011989' AND AJSTOR=581)
UNION
SELECT ILCATER.ILAISL AS AISLE, ILCATER.ILSLOT AS SLOT
FROM S216F06V.WDLSDATA.ILCATER ILCATER
WHERE (ILITEM='8011989' AND ILSTOR=581)

---

Related Topics