Revision as of 09:48, 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!

Source 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.

Source Code using PDO

TBD

Source Code using DB2 client

TBD

C

tbd

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 09:48, 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

Source Code using ODBC

Source Code using PDO

Source Code using DB2 client

C

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 09:48, 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

Source Code using ODBC

Source Code using PDO

Source Code using DB2 client

C

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