Revision as of 12:15, 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

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!^[1]

Create a database to play with

This is the third project Heiko did, so his personal Library was getting a bit cluttered. Therefore, let's create a library just for this niew project:
- CRTLIB LIB(ODBC) TEXT(Library for ODBC project source)
CRTSRCPF FILE(ODBC/ODBCDB) RCDLEN(112) TEXT('Project ODBC and Database') to create new physical file to hold the DDS.
WRKMBRPDM FILE(ODBC/ODBCDB) Work with this file.
F6 to create new member, insert member's name and type PF:
Enter this DDS: (Learn what the below means Especially time- and dateformats Even more about wibbly-wobbly-timey-wimey-stuff 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 *********************************************************************************************

14 to compile…

…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 ODBCDB to ODBCDBPF:
- And try again.
- Check with DSPMSG^[2] whether the compile was successful or not. If it did, you find the new PF in the library:

Insert content into the database

STRSQL + F4
- Enter the library name, in this example ODBC^[3]
In the interactive SQL session, enter:
INSERT INTO CALLS (CALLER, CALLED, DATE, TIME, TIMESTAMP) VALUES ('Ernie', 'Bert', '2019-10-05', '23.23', '2019-10-05 23:23:42')^[4]^[5]
There are many ways to display the result:
- If you're still in STRSQL, enter: SELECT * FROM CALLS
- On command line, try this: DSPF FILE(ODBC/ODBCDBPF)
- Or use STRDFU, select option 5 and fill out the displayed form.

ODBC Queries

Make sure you've set up /etc/odbc.ini correctly.

Perl

Here is a nice 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 odbc_ command set
- HowTo use PHP and ODBC^[6]
Use PDO
- Short Overview @PoC: Is this compatible???
Use Db2 driver @PoC: Is this compatible???
- Db2 client & direct link
- Installation instructions
- Usage and after client installation: TBD -h 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^[7]^[8]I'm pretty sure, its old 32bit ODBC-drivers conflicting with 64bit PHP^[9].

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 db2_connect(). netstat shows that the TCP-connection is ESTABLISHED. 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 unixodbc-dev

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

Output

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

                                  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:

↑ Heiko made this pretty brief, f.e. not every F3 or ⏎ is noted.
↑ or use Compile Sources Without Queuing
↑ Shortcut: STRSQL LIBOPT(ODBC)
↑ Timestamp format: https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0007107.html
↑ You can use either . or : as delimiter in time fields. The proper way, according to the documentation I found, is: .
↑ Sadly this is for mySQL. Db2 would be much more appreciated.
↑ php-mmap-issue: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted
↑ php-mmap-issue: https://stackoverflow.com/questions/21286589/linux-odbc-fatal-error-allowed-memory-size/21412667#21412667
↑ Oddly, the same drivers work with perl

[1] Heiko made this pretty brief, f.e. not every F3 or ⏎ is noted.

[2] r use Compile Sources Without Queuing

[3] Shortcut: STRSQL LIBOPT(ODBC)

[4] Timestamp format: https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0007107.html

[5] You can use either . or : as delimiter in time fields. The proper way, according to the documentation I found, is: .

[6] Sadly this is for mySQL. Db2 would be much more appreciated.

[7] -mmap-issue: https://stackoverflow.com/questions/42132558/odbc-prepare-gives-fatal-error-allowed-memory-size-exhausted

[8] -mmap-issue: https://stackoverflow.com/questions/21286589/linux-odbc-fatal-error-allowed-memory-size/21412667#21412667

[9] Oddly, the same drivers work with perl

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

User:Heiko: Difference between revisions

Revision as of 12:15, 16 October 2019

Contents

About

Personal information

Ideas for new articles

Project: Access database with ODBC

Create a database

Create a database to play with

Insert content into the database

ODBC Queries

Perl

Script Source Code

Output

PHP

PHP Code using ODBC

PHP Code using DB2 client

PHP Code using PDO

C

C Source Code

Compiling

Output

Articles to maintain

Sandbox

Screen mit MW-Bordmitteln

Und nun Screen mit Bordmitteln und etwas HTML-Foo

Using (not-working) console-tag

Navigation menu

User:Heiko: Difference between revisions

Revision as of 12:15, 16 October 2019

About

Personal information

Ideas for new articles

Project: Access database with ODBC

Create a database

Create a database to play with

Insert content into the database

ODBC Queries

Perl

Script Source Code

Output

PHP

PHP Code using ODBC

PHP Code using DB2 client

PHP Code using PDO

C

C Source Code

Compiling

Output

Articles to maintain

Sandbox

Screen mit MW-Bordmitteln

Und nun Screen mit Bordmitteln und etwas HTML-Foo

Using (not-working) console-tag

Navigation menu

Search