User:Heiko: Difference between revisions

From Try-AS/400
Jump to navigation Jump to search
Line 7: Line 7:
= Ideas for new articles =
= Ideas for new articles =


= Project: Access database with ODBC =
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.
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.
== 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>&#x23CE;</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&#x2026;<br/>[[Image:ODBCDB02.png|666px]]<br/>&#x2026;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 your ODBC connection (<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><ref>unixodbc-32/64bit drivers: http://www.unixodbc.org/doc/db2.html</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 =
= Articles to maintain =

Revision as of 21:58, 16 October 2019

About

This is Heikos personal page, in which he maybe shares some private information or collects bits for new articles.

Personal information

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

Articles to maintain


Sandbox

Screen mit MW-Bordmitteln

                                  Anmelden            
                                              System  . . . . . :   SLVRLAKE
                                              Subsystem . . . . :   QINTER
                                              Bildschirm  . . . :   QPADEV0004

               Benutzer  . . . . . . . . . . . .             
               Kennwort  . . . . . . . . . . . .
               Programm/Prozedur . . . . . . . .             
               Men}  . . . . . . . . . . . . . .             
               Aktuelle Bibliothek . . . . . . .             













                                       (C) COPYRIGHT IBM CORP. 1980, 2013.

Und nun Screen mit Bordmitteln und etwas HTML-Foo

Anmelden
System . . . . . : SLVRLAKE
Subsystem . . . . : QINTER
Bildschirm . . . : QPADEV0004

Benutzer . . . . . . . . . . . .
Kennwort . . . . . . . . . . . .
Programm/Prozedur . . . . . . . .
Men} . . . . . . . . . . . . . .
Aktuelle Bibliothek . . . . . . .













(C) COPYRIGHT IBM CORP. 1980, 2013.

Using (not-working) console-tag

<console> Test </console>

<console> 

                                  Anmelden            
                                              System  . . . . . :   SLVRLAKE
                                              Subsystem . . . . :   QINTER
                                              Bildschirm  . . . :   QPADEV0004

               Benutzer  . . . . . . . . . . . .             
               Kennwort  . . . . . . . . . . . .
               Programm/Prozedur . . . . . . . .             
               Men}  . . . . . . . . . . . . . .             
               Aktuelle Bibliothek . . . . . . .             













                                       (C) COPYRIGHT IBM CORP. 1980, 2013.

</console>

Retrieved from "http://try-as400.pocnet.net/index.php?title=User:Heiko&oldid=661"