Databases in Linux

[arcfide]

I cannot seem to see how to utilize databases in Dyalog 13.1 for Linux. What is the best process for doing this? It seems like SQAPL is the appropriate tool to use, but I read that it requires establishing a separate purchase?

[yaemmanuelli]

Hello,

I have just configured ODBC access on Ubuntu 20.04 to DB2 for Linux 11.5.
The DB2 server (including ODBC/CLI client) is installed on the same machine as Dyalog APL.
Here is what I did:

1. Install and configure unixODBC driver manager

1.1 Install the packages unixodbc and unixodbc-dev

For example, in a terminal window execute the following command:

Code: Select all

sudo apt-get install unixodbc unixodbc-dev

1.2 Locate the configuration file odbcinst.ini

Execute the following command

Code: Select all

odbcinst -j

It will return something like:

Code: Select all

unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/yae/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The full path of the configuration file is shown at the line DRIVERS............:

1.3 Configure unixODBC for your instance of DB2

Edit the file referenced by the line DRIVERS............: above
(i.e. /etc/odbcinst.ini) to add the following stanza:

Code: Select all

[Db2]
Description = Db2 Driver
Driver = <instance_path>/lib/libdb2o.so
fileusage=1
dontdlclose=1

<instance_path> is the instance owner's home directory, for example /home/db2inst1

The command invoking the editor should be prefixed by sudo:

Code: Select all

sudo gedit /etc/odbcinst.ini
sudo nano /etc/odbcinst.ini
sudo vi /etc/odbcinst.ini

2. Configure your ODBC user data source.

Edit the file .odbc.ini in your home directory (no sudo this time!).
Let's assume you want to access the database SAMPLE in the instance db2inst1.

2.1. Add the following line into the stanza [ODBC Data Sources]

Code: Select all

SAMPLE        = Db2 11.5 Driver

2.3 Add the following new stanza

Code: Select all

[SAMPLE]
Driver       = /home/db2inst1/sqllib/lib/libdb2o.so
Description  = Sample Db2 ODBC Database

3. Test the configuration

3.1. Start Dyalog APL

3.2 Execute the following

      )load sqapl
/opt/mdyalog/18.0/64/unicode/ws/sqapl.dws saved Thu Jul 16 02:13:00 2020
      SQA.Init ''
0  SQAPL loaded from: cxdya63u64v.so Using default translation no aplunicd.ini 
      present 
      SQA.DSN ''
0   myodbc53u  /usr/local/lib/libmyodbc5w.so        
    SAMPLE     /home/db2inst1/sqllib/lib/libdb2.so  
      SQA.Connect 'c1' 'SAMPLE'
0
      3⊃SQA.Do 'c1' 'select * from DB2INST1.STAFF fetch first 3 rows only'
 10  Sanders   20  Mgr    7 98357.5    0    
 20  Pernal    20  Sales  8 78171.25 612.45 
 30  Marenghi  38  Mgr    5 77506.75   0 
      SQA.Close '.'
0
      )off

4. Enjoy!

--
Yves-Antoine Emmanuelli.

[Morten|Dyalog]

Hi Yves-Antoine!

Can you tell me what output you see if you use a command-line tool (or any other tool at your disposal) to make the same query. Also, what is the data type of the last column in your selection?

Thanks!

Morten

[yaemmanuelli]

Hello Morten,

Thanks for pointing out this last column, which is of type DECIMAL(7,2):

Using Db2 Command Line Processor (CLP) in a Linux terminal:

Code: Select all

yae@t590:~$ db2 "select * from DB2INST1.STAFF fetch first 3 rows only"

ID     NAME      DEPT   JOB   YEARS  SALARY    COMM     
------ --------- ------ ----- ------ --------- ---------
    10 Sanders       20 Mgr        7  98357,50         -
    20 Pernal        20 Sales      8  78171,25    612,45
    30 Marenghi      38 Mgr        5  77506,75         -

  3 record(s) selected.


Code: Select all

yae@t590:~$ db2 "describe table DB2INST1.STAFF"

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
ID                              SYSIBM    SMALLINT                     2     0 No   
NAME                            SYSIBM    VARCHAR                      9     0 Yes   
DEPT                            SYSIBM    SMALLINT                     2     0 Yes   
JOB                             SYSIBM    CHARACTER                    5     0 Yes   
YEARS                           SYSIBM    SMALLINT                     2     0 Yes   
SALARY                          SYSIBM    DECIMAL                      7     2 Yes   
COMM                            SYSIBM    DECIMAL                      7     2 Yes   

  7 record(s) selected.


The dashes for column COMM in first and last rows stand for NULL values in database terms, i.e. no value. The 2nd row has the value 612.45

If using utility db2cli, the query will use DB2 CLI (Call Level Interface, used by ODBC), we get the same result, so the issue is in SQAPL:

Code: Select all

yae@t590:~$ db2cli execsql -execute -dsn SAMPLE -inputsql staff.sql
IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> select * from DB2INST1.STAFF fetch first 3 rows only;
FetchAll:  Columns: 7
  ID NAME DEPT JOB YEARS SALARY COMM
  10, Sanders, 20, Mgr  , 7, 98357,50, -
  20, Pernal, 20, Sales, 8, 78171,25, 612,45
  30, Marenghi, 38, Mgr  , 5, 77506,75, -
FetchAll: 3 rows fetched.

Code: Select all

yae@t590:~$ cat staff.sql
select * from DB2INST1.STAFF fetch first 3 rows only;


And if I execute the query from Dyalog APL filtering for NOT NULL values in column COMM:

      3⊃SQA.Do 'c1' 'select * from DB2INST1.STAFF where COMM is NOT NULL fetch first 3 rows only'
 20  Pernal   20  Sales     8 78171.25 612.45 
 40  O'Brien  38  Sales     0 78006    846.55 
 60  Quigley  38  Sales  1039 66808.3  650.25

... we get a correct result (612,45 for Pernal).

Thanks.
--
Yves-Antoine Emmanuelli.

[Morten|Dyalog]

The problem could be in SQAPL, but it could also be in the ODBC driver. We do not have DB2 available for testing, but when we create a similar table with an Oracle driver that we have, decimal data containing nulls *is* returned correctly. Do you have any other tools that use the ODBC driver at your disposal, so we could prove that it is not the driver causing the problem and that the problem does lie in SQAPL?

We created an Oracle table using:

"Create table TestNulls (ID NUMBER Generated by default as identity, Firstname varchar2(50), Lastname varchar2(50), Salary Number(7,2), Comm Number(7,2)"

[yaemmanuelli]

Hello Morten,

I have tried a more rigorous testcase using the lower level SQAPL functions, because SQA.Do does not support option 'nulls' (I am discovering your interface :-) :

      )load sqapl
/opt/mdyalog/18.0/64/unicode/ws/sqapl.dws saved Thu Jul 16 02:13:00 2020
      SQA.Init ''
0  SQAPL loaded from: cxdya63u64v.so Using default translation no aplunicd.ini 
      present 
      SQA.Connect 'C1' 'SAMPLE'
0
      SQA.Prepare 'C1.S1' 'select ID, COMM from DB2INST1.STAFF order by ID fetch first 9 rows only'
0
      SQA.Exec 'C1.S1'
0 ¯1
      SQA.Fetch 'C1.S1' ('nulls' 1)
0   10 0.0000E0       0 1   6
    20 0.0000E0       0 1    
    30 0.0000E0       0 1    
    40 8.4655E2       0 0    
    50 2.0000E¯323    0 0    
    60 6.5025E2       0 0    
    70 1.1520E3       0 0    
    80 1.2820E2       0 0    
    90 1.3867E3       0 0    
      SQA.Transact 'C1' 0
0
      SQA.Close 'C1.S1'
0
      SQA.Prepare 'C1.S1' 'select ID, COMM from DB2INST1.STAFF where COMM is NOT NULL order by ID fetch first 6 rows only'
0
      SQA.Exec 'C1.S1'
0 ¯1
      SQA.Fetch 'C1.S1' ('nulls' 1)
0   20  612.45    0 0   6
    40  846.55    0 0    
    60  650.25    0 0    
    70 1152       0 0    
    80  128.2     0 0    
    90 1386.7     0 0

We can see that, in the result of the first query, COMM is incorrectly seen as NULL for ID 20, and NOT NULL for ID 50.

I'll install an ODBC query tool on my machine, and report the same test ASAP.

Thanks.
--
Yves-Antoine Emmanuelli.

[yaemmanuelli]

Hello Morten,

Here are the same query executions with isql, a command line query tool shipped with unixodbc:

Code: Select all

yae@t590:~$ isql sample
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select ID, COMM from DB2INST1.STAFF order by ID fetch first 9 rows only
+-------+----------+
| ID    | COMM     |
+-------+----------+
| 10    | 10       |
| 20    | 612,45   |
| 30    | 30       |
| 40    | 846,55   |
| 50    | 50       |
| 60    | 650,25   |
| 70    | 1152,00  |
| 80    | 128,20   |
| 90    | 1386,70  |
+-------+----------+
SQLRowCount returns -1
9 rows fetched
SQL> select ID, COMM from DB2INST1.STAFF where COMM is NOT NULL order by ID fetch first 6 rows only
+-------+----------+
| ID    | COMM     |
+-------+----------+
| 20    | 612,45   |
| 40    | 846,55   |
| 60    | 650,25   |
| 70    | 1152,00  |
| 80    | 128,20   |
| 90    | 1386,70  |
+-------+----------+
SQLRowCount returns -1
6 rows fetched
SQL> quit
yae@t590:~$


For rows where COMM is NULL, the value for ID is reported for COMM, and the value for ID 20 is correct.

Hope it helps.

Regards.
--
Yves-Antoine Emmanuelli.

[yaemmanuelli]

Quick installation of Db2 on Linux.

Install the Linux kernel AIO access shared library (prerequisite).

Download Db2 Community edition for Linux from:
https://www.ibm.com/analytics/db2/trials
Link "Download for free"
(You'll have to create an IBM ID if you do not have one).

Extract the files from the archive.

Install the product:

Code: Select all

cd <subdirectory where db2_install resides>
sudo ./db2_install -f NOTSAMP

Create Linux users db2inst1 and db2fenc1

Create instance db2inst1:

Code: Select all

cd /opt/ibm/db2/V11.5/instance
sudo ./db2icrt -u db2fenc1 db2inst1

Post installation actions:

Code: Select all

su - db2inst1
db2start
db2sampl
db2 connect to SAMPLE
db2 grant CONNECT, DBADM on database to user <your preferred user>
db2 terminate
exit


Before starting Dyalog APL with <your preferred user>, execute:

Code: Select all

. ~db2inst1/sqllib/db2profile

The documentation is there:
https://www.ibm.com/support/knowledgece ... lcome.html

Regards.
--
Yves-Antoine Emmanuelli

[Morten|Dyalog]

Yves, thanks for running the experiments!

The way I read it, your last experiment suggests that this ODBC driver incorrectly identifies NULL values in query results. In your last example with the query tool, this leads to the value in the ID field being displayed in the COMM column, where you would expect to see a "-" or some other indicator that the value was NULL. With SQAPL, you get mostly zeros and some strange floating point values, but I think this is explained by us probably allocating / pre-initialised the buffer in a different way. My understanding is that we run through the data setting NULL values to 0, after the driver has populated the buffer with data and given us the NULL flags.

We may be able to investigate a bit further, but the evidence suggests that we won't be able to fix the problem, and that you need to report to the ODBC driver maker (IBM?) that it is misbehaving - I think you have some pretty clear evidence for them.

I hope this helps move things in the right direction! If you don't agree with my conclusions, please let me know!

Regards,

Morten

[yaemmanuelli]

Hello Morten,

I'll report this to a Db2 forum.

Regards.
--
Yves-Antoine Emmanuelli.