|
|
| (12 intermediate revisions by 2 users not shown) |
| Line 1: |
Line 1: |
| = About = | | == About == |
| This is [[User:Heiko|Heiko]]s personal page, in which he maybe shares some private information or collects bits for new articles. | | This is [[User:Heiko|Heiko]]s personal page, in which he maybe shares some private information or collects bits for new articles. |
|
| |
|
| == Personal information == | | === Personal Information === |
| [[User:Heiko|Heiko]] has been in IT since the 90s, but never worked with an AS/400…until 2019. He'll document his first steps in the hope they will be useful to others. | | [[User:Heiko|Heiko]] has been in IT since the 90s, but never worked with an AS/400…until 2019. He'll document his first steps in the hope they will be useful to others. |
|
| |
|
| = Ideas for new articles = | | == Ideas for new articles == |
| | tbd |
|
| |
|
| | | == Articles to maintain == |
| = Project: Access database with ODBC = | | * [[:Category:Programming]] |
| Accessing a AS/400 via ODBC is something that might come in handy some day, so I decided to explore how it can be done.
| | ** [[99 Bottles Of Beer (using REXX)]] |
| | | ** [[Hello World (using C)]] |
| Be warned: This is more like a log, showing (and hopefully solving) all issues I ran into doing this. I think this is more helpful than a ideal world example.
| | ** [[Hello World (using database, display file and RPG)]] |
| | | ** [[Create a database and query it using ODBC from a linux machine]] <-- NEEDS A LOT OF WORK IN THE PHP DEPARTMENT! |
| == Create a database ==
| |
| I suggest you try [[Beginners Project: 99 Bottles Of Beer (using REXX)]] and [[Beginners Project: Hello World (using database, display file and RPG)]] before you try this!<ref>[[User:Heiko|Heiko]] made this pretty brief, f.e. not every <code>F3</code> or <code>⏎</code> is noted.</ref><br/>
| |
| | |
| === Create a database to play with ===
| |
| * This is the third project [[User:Heiko|Heiko]] did, so his personal Library was getting a bit cluttered. Therefore, let's create a library just for this niew project:
| |
| ** <code>CRTLIB LIB(ODBC) TEXT(Library for ODBC project source)</code>
| |
| * <code>CRTSRCPF FILE(ODBC/ODBCDB) RCDLEN(112) TEXT('Project ODBC and Database')</code> to create new physical file to hold the DDS.
| |
| * <code>WRKMBRPDM FILE(ODBC/ODBCDB)</code> Work with this file. | |
| * <code>F6</code> to create new member, insert member's name and type <code>PF</code>:<br/>[[Image:ODBCDB01.png|666px]] | |
| * Enter this DDS: ([https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/rzakc/rzakcdatel.htm Learn what the below means] [https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_74/rzasb/cpdtex.htm Especially time- and dateformats] [https://www.db2tutorial.com/db2-basics/db2-time/ Even more about wibbly-wobbly-timey-wimey-stuff] [https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/intro/src/tpc/db2z_datetimetimestamp.html and more])
| |
| ***************** Datenanfang *******************************************************************************************
| |
| 0001.00 A R CALLS 191002
| |
| 0002.00 A CALLER 42A 191002
| |
| 0003.00 A CALLED 42A 191002
| |
| 0004.00 A DATE L TIMFMT(*ISO) 191002
| |
| 0005.00 A TIME T 191002
| |
| 0006.00 A TIMESTAMP Z 191002
| |
| ******************Datenende *********************************************************************************************
| |
| * <code>14</code> to compile…<br/>[[Image:ODBCDB02.png|666px]]<br/>…I failed again. Stupid me. I can't create a new physical database file in the same place, where source physical file (with the same name!) exists.
| |
| ** Just rename the DDS member from <code>ODBCDB</code> to <code>ODBCDBPF</code>:<br/>[[Image:ODBCDB03.png|666px]]<br/>[[Image:ODBCDB04.png|666px]]
| |
| ** And try again. | |
| ** Check with <code>DSPMSG</code><ref>or use [[Compile Sources Without Queuing]]</ref> whether the compile was successful or not. If it did, you find the new PF in the library:<br/>[[Image:ODBCDB10.png|666px]]
| |
| | |
| === Insert content into the database ===
| |
| * <code>STRSQL</code> + <code>F4</code>
| |
| ** Enter the library name, in this example <code>ODBC</code><ref>Shortcut: <code>STRSQL LIBOPT(ODBC)</code></ref>
| |
| * In the interactive SQL session, enter:<br/><code>INSERT INTO CALLS (CALLER, CALLED, DATE, TIME, TIMESTAMP) VALUES ('Ernie', 'Bert', '2019-10-05', '23.23', '2019-10-05 23:23:42')</code><ref>Timestamp format: https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0007107.html</ref><ref>You can use either <code>.</code> or <code>:</code> as delimiter in time fields. The proper way, according to the documentation I found, is: <code>.</code></ref>
| |
| * There are many ways to display the result:
| |
| ** If you're still in STRSQL, enter: <code>SELECT * FROM CALLS</code>
| |
| ** On command line, try this: <code>DSPF FILE(ODBC/ODBCDBPF)</code>
| |
| ** Or use <code>STRDFU</code>, select option 5 and fill out the displayed form.
| |
| [[Image:ODBC_DSPF01.png|666px|Displayed using <code>DSPF</code>]]
| |
| | |
| == ODBC Queries ==
| |
| Make sure you've set up <tt>/etc/odbc.ini</tt> correctly.
| |
| === Perl ===
| |
| Here is a nice [https://www.ibm.com/developerworks/data/library/techarticle/dm-0512greenstein/index.html HowTo] from IBM. Worth reading.
| |
| ==== Script Source Code ====
| |
| #!/usr/bin/perl
| |
| use strict;
| |
| use DBI;
| |
|
| |
| my $db_cfg="YOURCONFIG"; # See your /etc/odbc.ini
| |
| my $db_pwd="YOURUSER";
| |
| my $db_user="YOURPASSWORD";
| |
|
| |
| printf("Connecting to source database on $db_cfg...\n");
| |
| my $dbh = DBI->connect('DBI:ODBC:' . $db_cfg, $db_user, $db_pwd, {PrintError => 1, AutoCommit => 1}); # Connecting to Db2 database
| |
| if ( $dbh )
| |
| {
| |
| my $sth = $dbh->prepare ("SELECT * FROM ODBC/ODBCDBPF");
| |
| if (!$sth->execute)
| |
| {
| |
| printf("Error: Preparing select on source failed.\n");
| |
| die;
| |
| }
| |
|
| |
| my $fCaller;
| |
| my $fCalled;
| |
| my $fDate;
| |
| my $fTime;
| |
| my $fTS;
| |
| while ( ($fCaller, $fCalled, $fDate, $fTime, $fTS) = $sth->fetchrow ) # Read one row from source table
| |
| {
| |
| printf("%s, %s, %s, %s, %s\n", $fCaller, $fCalled, $fDate, $fTime, $fTS);
| |
| }
| |
|
| |
| # Clean up last statements
| |
| $sth->finish;
| |
|
| |
| # Disconnect from database
| |
| if ( $dbh )
| |
| {
| |
| print("Disconnecting from $db_cfg...\n");
| |
| $dbh->disconnect;
| |
| }
| |
| }
| |
| else
| |
| {
| |
| printf("Error: Connection to database (%s) failed:\n%s\n%s\n%s\n", $db_cfg, $DBI::err, $DBI::errstr, $DBI::state);
| |
| die;
| |
| }
| |
| | |
| ==== Output ====
| |
| $ ./as400-odbc.pl
| |
| Connecting to source database on YOURCONFIG...
| |
| Ernie Bert 2019-10-05 23:24:00 2019-10-05 23:23:42.000000
| |
| Bob Heppo 2019-11-06 05:05:05 2019-10-05 23:23:42.000000
| |
| Disconnecting from YOURCONFIG...
| |
| | |
| === PHP ===
| |
| I found several possible approaches:
| |
| * Use <code>odbc_</code> command set
| |
| ** [https://www.ibm.com/developerworks/library/os-php-odbc/index.html HowTo use PHP and ODBC]<ref>Sadly this is for mySQL. Db2 would be much more appreciated.</ref>
| |
| * Use <tt>PDO</tt>
| |
| ** [https://www.php.net/manual/en/ref.pdo-ibm.php Short Overview] '''''@PoC: Is this compatible???'''''
| |
| * Use Db2 driver '''''@PoC: Is this compatible???'''''
| |
| ** [https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.doc/connecting/connect_driver_package.html Db2 client] & [https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/ direct link]
| |
| ** [https://github.com/php/pecl-database-ibm_db2 Installation instructions]
| |
| ** [https://www.php.net/manual/en/function.db2-connect.php Usage] and after client installation: <code>TBD -h</code> might help!
| |
| | |
| ==== PHP Code using ODBC ====
| |
| Let me say right away, that didn't work out as easy as I hoped.
| |
| <?php
| |
|
| |
| /* Sample Code: How to query a Db2 database on a AS/400 (iSeries)
| |
| * Note: apt-get install php-odbc might be necessary
| |
| * About: https://try-as400.pocnet.net
| |
| * */
| |
|
| |
| $db_cfg="YOURCONFIG"; # See your /etc/odbc.ini
| |
| $db_user="YOURUSER";
| |
| $db_pwd="YOURPASSWORD";
| |
|
| |
| $dbh=odbc_connect($db_cfg, $db_user, $db_pwd);
| |
| if ($dbh)
| |
| {
| |
| $sql="SELECT * FROM ODBC/ODBCDBPF";
| |
| printf("Preparing \"$sql\"...\n");
| |
| $sth=odbc_prepare($dbh, $sql);
| |
| | |
| As you can see, the source isn't complete. That's because I wasn't able to execute it any further. Output so far:
| |
| Preparing "SELECT * FROM ODBC/ODBCDBPF"...
| |
|
| |
| mmap() failed: [12] Cannot allocate memory
| |
|
| |
| PHP Fatal error: Out of memory (allocated 2097152) (tried to allocate 206158430251 bytes)
| |
| The driver seems to need 206158430251 bytes, that's 192GB, a bit greedy for my taste.
| |
| | |
| After some research<ref>php-mmap-issue: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted</ref><ref>php-mmap-issue: https://stackoverflow.com/questions/21286589/linux-odbc-fatal-error-allowed-memory-size/21412667#21412667</ref>I'm pretty sure, its old 32bit ODBC-drivers conflicting with 64bit PHP<ref>Oddly, the same drivers work with perl</ref>.
| |
| | |
| STAY TUNED WE ARE WORKING ON A SOLUTION.
| |
| | |
| ==== PHP Code using DB2 client ====
| |
| That wasn't very smooth, either.
| |
| | |
| I first installed some software:
| |
| * Get IBM's CLI driver: https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/ and move it to /home/clidriver
| |
| * Insert this into .bashrc:
| |
| # CLI Driver (PHP -> Db2 (AS/400),
| |
| export IBM_DB_HOME=/home/clidriver
| |
| export LD_LIBRARY_PATH=${IBM_DB_HOME}/lib
| |
| export PATH=${IBM_DB_HOME}/bin:$PATH
| |
| * apt-get install php-dev
| |
| * pecl install ibm_db2
| |
| * add extension=ibm_db2.so to /etc/php/7.0/cli/php.ini
| |
| * If used with apache (untested):
| |
| ** add LD_LIBRARY_PATH as shown above to httpd.conf
| |
| ** add extension=ibm_db2.so to /etc/php/7.0/apache2/php.ini
| |
| ** Beyond: https://www.php.net/manual/en/ibm-db2.installation.php || https://github.com/php/pecl-database-ibm_db2 || https://public.dhe.ibm.com/ibmdl/export/pub/software/data/db2/drivers/odbc_cli/
| |
| | |
| $db_user="YOURUSER";
| |
| $db_pwd="YOURPASSWORT";
| |
| $db_db = 'ODBC';
| |
| $db_hostname = 'silverlake.intern.mathpeter.com';
| |
| $db_port = 8471; //50000; /* https://www.ibm.com/support/pages/tcpip-ports-required-ibm-i-access-and-related-functions */
| |
| $db_options = array('autocommit' => DB2_AUTOCOMMIT_ON);
| |
|
| |
| ini_set("display_errors", 1);
| |
|
| |
| //$connectionString = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=$db_db;HOSTNAME=$db_hostname;PORT=$db_port;PROTOCOL=TCPIP;UID=$db_user;PWD=$db_pwd;"; // takes for ever to connect
| |
| $connectionString = "DATABASE=$db_db;HOSTNAME=$db_hostname;PORT=$db_port;PROTOCOL=TCPIP;UID=$db_user;PWD=$db_pwd;"; // takes for ever to connect
| |
| printf("connectionString: $connectionString\n");
| |
| $dbh=db2_connect($connectionString, NULL, NULL, $db_options); // https://www.php.net/manual/en/function.db2-connect.php
| |
| | |
| This code stalls at <code>db2_connect()</code>. <tt>netstat</tt> shows that the TCP-connection is <tt>ESTABLISHED</tt>. It doesn't create heavy load or traffic. It doesn't run into a timeout.
| |
| | |
| STAY TUNED - MAYBE I CAN GET THIS WORKING.
| |
| | |
| ==== PHP Code using PDO ====
| |
| TBD
| |
| | |
| === C ===
| |
| Nice to know:
| |
| * http://www.unixodbc.org
| |
| * http://www.unixodbc.org/doc/db2.html
| |
| * https://www.easysoft.com/developer/interfaces/odbc/linux.html
| |
| * https://www.easysoft.com/developer/languages/c/odbc_tutorial.html
| |
| * https://stackoverflow.com/questions/33020646/gcc-odbc-on-linux-is-not-linking
| |
| * You might need to install <tt>unixodbc-dev</tt>
| |
| ==== C Source Code ====
| |
| /* File: as400-odbc.c
| |
| * About: Learn more here: https://try-as400.pocnet.net
| |
| */
| |
|
| |
| #include <stdio.h>
| |
| #include <stdlib.h>
| |
| #include <sql.h>
| |
| #include <sqlext.h>
| |
| #include <string.h>
| |
|
| |
| #define RESULT_LEN 256
| |
|
| |
| char *odbcConnectionString = "DSN=YOURCONFIG;UID=YOURLOGIN;PWD=YOURPASSWORD;"; // See /etc/odbc.ini for DSN, use your AS/400 account as credentials.
| |
|
| |
| #define CHECK_ERROR(e, s, h, t)\
| |
| (\
| |
| {\
| |
| if (e!=SQL_SUCCESS && e != SQL_SUCCESS_WITH_INFO)\
| |
| { extract_error(s, h, t); goto exit;}\
| |
| }\
| |
| )
| |
|
| |
| void extract_error(char *fn, SQLHANDLE handle, SQLSMALLINT type)
| |
| {
| |
| SQLINTEGER i = 0;
| |
| SQLINTEGER NativeError;
| |
| SQLCHAR SQLState[7];
| |
| SQLCHAR MessageText[256];
| |
| SQLSMALLINT TextLength;
| |
| SQLRETURN ret;
| |
|
| |
| fprintf(stderr, "\nThe driver reported the following error %s\n", fn);
| |
| do
| |
| {
| |
| ret = SQLGetDiagRec(type, handle, ++i, SQLState, &NativeError,
| |
| MessageText, sizeof(MessageText), &TextLength);
| |
| if (SQL_SUCCEEDED(ret)) {
| |
| printf("%s:%ld:%ld:%s\n",
| |
| SQLState, (long)i, (long)NativeError, MessageText);
| |
| }
| |
| } while (ret == SQL_SUCCESS);
| |
| }
| |
|
| |
| int main()
| |
| {
| |
|
| |
| SQLHENV henv = SQL_NULL_HENV; // Environment
| |
| SQLHDBC hdbc = SQL_NULL_HDBC; // Connection handle
| |
| SQLHSTMT hstmt = SQL_NULL_HSTMT; // Statement handle
| |
| SQLRETURN retcode;
| |
| SQLCHAR fCaller[RESULT_LEN];
| |
| SQLCHAR fCalled[RESULT_LEN];
| |
| SQLCHAR fDate[RESULT_LEN];
| |
| SQLCHAR fTime[RESULT_LEN];
| |
| SQLCHAR fTS[RESULT_LEN];
| |
| SQLCHAR outstr[1024];
| |
| SQLSMALLINT outstrlen;
| |
| int i = 0;
| |
|
| |
| // Allocate environment handle
| |
| retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
| |
| CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_ENV)", henv, SQL_HANDLE_ENV);
| |
|
| |
| // Set the ODBC version environment attribute
| |
| retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
| |
| CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)", henv, SQL_HANDLE_ENV);
| |
|
| |
| retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3, 0);
| |
| CHECK_ERROR(retcode, "SQLSetEnvAttr(SQL_ATTR_ODBC_VERSION)", henv, SQL_HANDLE_ENV);
| |
|
| |
| // Allocate connection handle
| |
| retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
| |
| CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_DBC)", hdbc, SQL_HANDLE_DBC);
| |
|
| |
| // Set login timeout to 5 seconds
| |
| SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
| |
| CHECK_ERROR(retcode, "SQLSetConnectAttr(SQL_LOGIN_TIMEOUT)", hdbc, SQL_HANDLE_DBC);
| |
|
| |
| // Connect to data source, replace with your connection string
| |
| retcode = SQLDriverConnect(hdbc, NULL, odbcConnectionString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
| |
|
| |
| // Allocate statement handle
| |
| retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
| |
| CHECK_ERROR(retcode, "SQLAllocHandle(SQL_HANDLE_STMT)", hstmt, SQL_HANDLE_STMT);
| |
|
| |
| // Execute SQL Query
| |
| retcode = SQLExecDirect(hstmt, (SQLCHAR*) "SELECT * FROM ODBC/ODBCDBPF", SQL_NTS);
| |
| CHECK_ERROR(retcode, "SQLExecDirect()", hstmt, SQL_HANDLE_STMT);
| |
|
| |
| // Bind columns
| |
| retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, &fCaller, RESULT_LEN, 0);
| |
| retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, &fCalled, RESULT_LEN, 0);
| |
| retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, &fDate, RESULT_LEN, 0);
| |
| retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, &fTime, RESULT_LEN, 0);
| |
| retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, &fTS, RESULT_LEN, 0);
| |
|
| |
| // Fetch and print each row of data until SQL_NO_DATA returned.
| |
| for (i = 0; ; i++)
| |
| {
| |
| retcode = SQLFetch(hstmt);
| |
| if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
| |
| {
| |
| printf("Row %d: %s\t%s\t%s\t%s\t%s\n", i, fCaller, fCalled, fDate, fTime, fTS);
| |
| }
| |
| else
| |
| {
| |
| if (retcode != SQL_NO_DATA)
| |
| {
| |
| CHECK_ERROR(retcode, "SQLFetch()", hstmt, SQL_HANDLE_STMT);
| |
| }
| |
| else
| |
| {
| |
| break;
| |
| }
| |
| }
| |
| }
| |
|
| |
| exit:
| |
|
| |
| //
| |
| // Free handles
| |
| //
| |
| // Statement
| |
| if (hstmt != SQL_NULL_HSTMT)
| |
| {
| |
| SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
| |
| }
| |
|
| |
| // Connection
| |
| if (hdbc != SQL_NULL_HDBC)
| |
| {
| |
| SQLDisconnect(hdbc);
| |
| SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
| |
| }
| |
|
| |
| // Environment
| |
| if (henv != SQL_NULL_HENV)
| |
| {
| |
| SQLFreeHandle(SQL_HANDLE_ENV, henv);
| |
| }
| |
|
| |
| return 0;
| |
| }
| |
| | |
| ==== Compiling ====
| |
| #!/bin/bash
| |
|
| |
| clear
| |
| gcc as400-odbc.c -o as400-odbc_c -lodbc
| |
| Note the <code>-lodbc</code> for linking against the ODBC-libraries.
| |
| | |
| ==== Output ====
| |
| $./as400-odbc_c
| |
| Row 0: Ernie Bert 2019-10-05 23:24:00 2019-10-05 23:23:42.000000
| |
| Row 1: Bob Heppo 2019-11-06 05:05:05 2019-10-05 23:23:42.000000
| |
| $
| |
| | |
| \o/
| |
| | |
| = Articles to maintain =
| |
| * [[Basic Software Development]]
| |
| ** [[Beginners Project: 99 Bottles Of Beer (using REXX)]]
| |
| ** [[Beginners Project: Hello World (using C)]]
| |
| ** [[Beginners Project: Hello World (using database, display file and RPG)]]
| |
| * [[Editing Source Files using Eclipse and FTP]] | | * [[Editing Source Files using Eclipse and FTP]] |
| * [[Compile Sources Without Queuing]] | | * [[Compile Sources Without Queuing]] |
| | * [[How to solve Variadic Arguments issue (using C)]] |
|
| |
|
| | == Too many F-Keys to memorize == |
| | I'm using a small apple keyboard on which the F-keys aren't labeled very well. This helps a lot: |
|
| |
|
| | [[Image:F-Tasten F13-F24 v2 Photo.jpg|666px]] |
| | |
| | You can download it here: [[File:F-Tasten F13-F24 v2.pdf]] |
|
| |
|
| == Sandbox == | | == Sandbox == |
| Line 421: |
Line 83: |
| </p> | | </p> |
|
| |
|
| === Using (not-working) console-tag === | | == See also == |
| <console>
| | * [[Basic Commands]] |
| Test
| |
| </console>
| |
|
| |
|
| <console>
| | == Footnotes == |
| | |
| Anmelden
| |
| System . . . . . : SLVRLAKE
| |
| Subsystem . . . . : QINTER
| |
| Bildschirm . . . : QPADEV0004
| |
|
| |
| Benutzer . . . . . . . . . . . .
| |
| Kennwort . . . . . . . . . . . .
| |
| Programm/Prozedur . . . . . . . .
| |
| Men} . . . . . . . . . . . . . .
| |
| Aktuelle Bibliothek . . . . . . .
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
|
| |
| (C) COPYRIGHT IBM CORP. 1980, 2013.
| |
| </console>
| |
| | |
| ----
| |
| * Links
| |
| ** [[Basic Commands]]
| |
| * References:
| |
| <references/> | | <references/> |
|
| |
| [[Category:Basic Knowledge]]
| |
