Databases in Linux

Using Dyalog under 'nixes

Databases in Linux

Postby arcfide on Mon Sep 24, 2012 10:38 pm

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?
arcfide
 
Posts: 18
Joined: Fri Dec 09, 2011 3:53 am

Re: Databases in Linux

Postby yaemmanuelli on Thu Aug 13, 2020 9:28 am

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.
Last edited by yaemmanuelli on Fri Sep 04, 2020 10:48 am, edited 4 times in total.
--
Yves-Antoine Emmanuelli
User avatar
yaemmanuelli
 
Posts: 8
Joined: Sat Aug 01, 2020 6:29 pm
Location: Recloses, France

Re: Databases in Linux

Postby Morten|Dyalog on Thu Aug 13, 2020 10:13 am

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
User avatar
Morten|Dyalog
 
Posts: 385
Joined: Tue Sep 09, 2008 3:52 pm

Re: Databases in Linux

Postby yaemmanuelli on Thu Aug 13, 2020 3:36 pm

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.
--
Yves-Antoine Emmanuelli
User avatar
yaemmanuelli
 
Posts: 8
Joined: Sat Aug 01, 2020 6:29 pm
Location: Recloses, France

Re: Databases in Linux

Postby Morten|Dyalog on Fri Aug 14, 2020 1:08 pm

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)"
User avatar
Morten|Dyalog
 
Posts: 385
Joined: Tue Sep 09, 2008 3:52 pm

Re: Databases in Linux

Postby yaemmanuelli on Sun Aug 16, 2020 4:56 pm

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.
--
Yves-Antoine Emmanuelli
User avatar
yaemmanuelli
 
Posts: 8
Joined: Sat Aug 01, 2020 6:29 pm
Location: Recloses, France

Re: Databases in Linux

Postby yaemmanuelli on Sun Aug 16, 2020 5:32 pm

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.
--
Yves-Antoine Emmanuelli
User avatar
yaemmanuelli
 
Posts: 8
Joined: Sat Aug 01, 2020 6:29 pm
Location: Recloses, France

Re: Databases in Linux

Postby yaemmanuelli on Sun Aug 16, 2020 9:47 pm

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
--
Yves-Antoine Emmanuelli
User avatar
yaemmanuelli
 
Posts: 8
Joined: Sat Aug 01, 2020 6:29 pm
Location: Recloses, France

Re: Databases in Linux

Postby Morten|Dyalog on Mon Aug 17, 2020 6:16 am

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
User avatar
Morten|Dyalog
 
Posts: 385
Joined: Tue Sep 09, 2008 3:52 pm

Re: Databases in Linux

Postby yaemmanuelli on Mon Aug 17, 2020 9:42 am

Hello Morten,

I'll report this to a Db2 forum.

Regards.
--
Yves-Antoine Emmanuelli.
--
Yves-Antoine Emmanuelli
User avatar
yaemmanuelli
 
Posts: 8
Joined: Sat Aug 01, 2020 6:29 pm
Location: Recloses, France

Next

Return to UNIX and Linux

Who is online

Users browsing this forum: No registered users and 1 guest