Hi,

I have a Linux web server running Apache/PHP with DB2 7.1 for Linux installed. I can pull data off an AS/400 connected via the DB2 under Linux in simple queries - it was a job to even get THAT connectivity working. One problem though, I cannot update data on the AS/400 at all. No UPDATE, INSERT or DELETE statement works. I know you have to give the library name in the SQL query for the SQL statement to work. For example, on the statement:

UPDATE LIBRARY.FILE1 Set FIELD='100' WHERE FIELD2='10000'

I get an error 'SQL7008N REXX variable "FILE1 " contains inconsistent data.' where "FILE1" is just the AS/400 file (or table) name that I want to update.

I also get problems on trying an SQL JOIN too. Trying the following SQL:

SELECT * FROM LIBRARY.FILE1, LIBRARY.FILE2 WHERE LIBRARY.FILE1.FIELD1 = LIBRARY.FILE2.FIELD1 AND LIBRARY.FILE1.FIELD1='100000'

I get the error 'SQL0204N "LIBRARY.FILE2" is an undefined name.' when the table/file and all fields referenced DO exist. I tried this method too:

SELECT * FROM LIBRARY.FILE1 JOIN LIBRARY.FILE2 ON LIBRARY.FILE1.FIELD1=LIBRARY.FILE2.FIELD1 WHERE LIBRARY.FILE1.FIELD1='100000'

and I get the same error.

So, has anyone had these sorts of problems in getting PHP with DB2 on AS/400 functionality working?
Any help would be very much appreciated.

Thanks,

Derek

    I have a Windows 2000 Server running IIS and DBII 7. Can i make SQL query via PHP??
    Thanks,
    Lorenzo

      When you did the CATALOG command, you're actively connect to the database itself.

      Therefore, you should be able to do

      SELECT * from FILE1 WHERE...

      I'm not on AS400 myself, but under other OSes
      you can say "database.table.field" and
      if you CATALOG your sources then you're bypassing the "database" part and can access by table.field

      'Luck

      -Szii

        Yea, that's on other OS's .. on the AS/400 you have to give the library name else it uses the logged in user as the library name. I did actually just solve that problem .. I was working on the information I was given (assured to me to be correct), and it was actually incorrect and the 'FILE2' in question did not exist. Unfortunately being at the mercy of the bizarre naming conventions for tables here has its problems it seems. I think there's a limitation on the number of characters for a file name that leads to cryptic names like IBTMBL1 and ICITMSL1 etc.

        So it seems I was worrying about nothing with regards to the SELECT and JOIN problem. Doh.

        Still, the problem updating tables/files is still a problem I just cannot do it. I can even do CREATE TABLE and make a file with multiple fields and primary key that is accessible and workable on the AS/400 but I cannot insert or update any data into it.

        Thankyou for getting back to me though,

        Derek

          I have a Linux web server running Apache/PHP with DB2 7.1 for Linux
          installed. I can pull data off an AS/400 connected via the DB2 under
          Linux in simple queries - it was a job to even get THAT connectivity
          working.

          I am sure it was--how about telling us how you did it, and what worked?
          Better yet, maybe an article? :-) :-) :-)

            Umm.. okay. I might not be good at explaining this but I'll try.

            The information I found out came from a nice guy called Remo whom I found had written these steps in an email. We emailed back and forth for a while about it and I had success with reading data off the AS/400. Unfortunately we both had the same problem in being unable to insert/update/delete records so I don't know if that will ever get sorted out. Still, you can read data off the AS/400 using SELECT statements.

            Okay.. so, my notes that I made about it.

            IBM AS/400 DB2 Connect to Linux Apache/PHP

            Install DB2 Connect Personal (or Enterprise) Edition on Linux WWW machine.

            Compile PHP with DB2 Support (for appropriate version - the example given is for version 7.1).

            Add this option to your PHP compile options

            --with-ibm-db2=/usr/IBMdb2/7.1/include/

            Create DB2 Instance on Linux machine. Use the 'db2setup' command in the 'install' subdirectory of the DB2 Connect installation. Set up the account but not with the defaults, pick an original password at least.
            e.g.
            user db2as400
            password ?????

            Set up a user on the AS/400 and keep username and password for later to use in place of [as400user] and [as400pass]. Set that user's CCSID to '037' (for USA), do not leave it at 65535 (possibly system default). Make sure permissions are correct for any tables that need to be read or written to.

            Configure DB2 Instance by logging into Linux machine as DB2 user and password (as defined above) (or by su'ing from root to db2 account). Use command 'db2' to enter command line processor. From there, type (on one line per statement):

            (Items in [] are the user definable settings - don't include the [], just make up different names that are unique)

            catalog tcpip node [localName] remote 192.168.4.4 server 446 remote instance [youras400] system www ostype OS400

            catalog dcs database [as400DB] as [youras400]
            or
            catalog dcs database [as400DB] as [youras400] parms ",,,,,,,,BIDI=037"

            catalog database [as400DB] as [DSN] at node [localName] authentication DCS

            connect to [DSN] user [as400user] using [as400pass]

            bind /usr/IBMdb2/V7.1/bnd/@ddcs400.lst BLOCKING ALL SQLERROR CONTINUE MESSAGES DDCS400.MGS GRANT PUBLIC

            connect reset

            terminate

            Example PHP and SQL code:

            $sql = "SELECT * FROM AS400LIBRARY.AS400FILE";

            if ($conn_id = odbc_connect("[DSN]", "[as400user]", "[as400pass]")) {
            if ($result=odbc_do($conn_id, $sql)) {
            odbc_result_all($result);
            odbc_free_result($result);
            } else {
            echo "cannot execute '$sql'<br>";
            }
            odbc_close($conn_id);
            } else {
            echo "can not connect to DSN: $dsn<br>";
            }

            You should then be able to retrieve data from the AS/400 using normal SQL SELECT commands. Note that the library that the AS/400 file is in has to be in every statement.

              a month later

              Not sure if you still have these problems, but the issue is journaling. Either the file isn't journaled you are trying to write to or you don't have access to write to the journaling file. You have 2 choices to get around the problem:

              1) Turn on journaling on the file in question and make sure you have the proper access.

              2) Add the command "with none" on the end of every write type SQL command. This tells the query to not use journaling...

                Wow. That's just wonderful. That's all the information I needed, thankyou very much 🙂

                I had heard from someone else I'd been corresponding with that enabling journaling makes the problem disappear, so we were going to test out journaling on some text AS400 files. I had no idea there was such a simple an option as 'WITH NONE'. I've tried all my test queries with this option and I can INSERT, UPDATE and DELETE successfully.

                Thankyou again.

                Derek

                PS: Maybe some sort of AS/400 w/Linux & PHP FAQ could be prepared, I'd certainly be happy to get it started if noone else has one.

                  6 months later

                  I wish to do querys too in an as400 and i wish to know how do you do it?.
                  thanks in advance.

                  Derek Piper wrote:

                  Wow. That's just wonderful. That's all the information I needed, thankyou very much 🙂

                  I had heard from someone else I'd been corresponding with that enabling journaling makes the problem disappear, so we were going to test out journaling on some text AS400 files. I had no idea there was such a simple an option as 'WITH NONE'. I've tried all my test queries with this option and I can INSERT, UPDATE and DELETE successfully.

                  Thankyou again.

                  Derek

                  PS: Maybe some sort of AS/400 w/Linux & PHP FAQ could be prepared, I'd certainly be happy to get it started if noone else has one.

                    25 days later

                    Hello;
                    I have to tarnsfer some data from IBM AS/400 DB2 to FreeBSD MySQL and show them on Apache web server using PHP. I want to learn to transfer data from AS/400 (DB2) to FreeBSD (MySQL). Where can i find information about this?

                    Derek Piper wrote:

                    Hi,

                    I have a Linux web server running Apache/PHP with DB2 7.1 for Linux installed. I can pull data off an AS/400 connected via the DB2 under Linux in simple queries - it was a job to even get THAT connectivity working. One problem though, I cannot update data on the AS/400 at all. No UPDATE, INSERT or DELETE statement works. I know you have to give the library name in the SQL query for the SQL statement to work. For example, on the statement:

                    UPDATE LIBRARY.FILE1 Set FIELD='100' WHERE FIELD2='10000'

                    I get an error 'SQL7008N REXX variable "FILE1 " contains inconsistent data.' where "FILE1" is just the AS/400 file (or table) name that I want to update.

                    I also get problems on trying an SQL JOIN too. Trying the following SQL:

                    SELECT * FROM LIBRARY.FILE1, LIBRARY.FILE2 WHERE LIBRARY.FILE1.FIELD1 = LIBRARY.FILE2.FIELD1 AND LIBRARY.FILE1.FIELD1='100000'

                    I get the error 'SQL0204N "LIBRARY.FILE2" is an undefined name.' when the table/file and all fields referenced DO exist. I ....

                      4 months later

                      Hi,

                      Would you like to tell me how can i connect to as400 via DB2. I have span two week and still can't to as400

                      Thaks for your help

                      Vincent

                        Write a Reply...