Databases in Linux
12 posts
• Page 1 of 2 • 1, 2
Databases in Linux
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: 19
- Joined: Fri Dec 09, 2011 3:53 am
Re: Databases in Linux
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:
1.2 Locate the configuration file odbcinst.ini
Execute the following command
It will return something like:
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:
<instance_path> is the instance owner's home directory, for example /home/db2inst1
The command invoking the editor should be prefixed by sudo:
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]
2.3 Add the following new stanza
3. Test the configuration
3.1. Start Dyalog APL
3.2 Execute the following
4. Enjoy!
--
Yves-Antoine Emmanuelli.
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
Yves-Antoine Emmanuelli
-
yaemmanuelli - Posts: 16
- Joined: Sat Aug 01, 2020 6:29 pm
- Location: Recloses, France
Re: Databases in Linux
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
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
-
Morten|Dyalog - Posts: 458
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Databases in Linux
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:
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:
And if I execute the query from Dyalog APL filtering for NOT NULL values in column COMM:
... we get a correct result (612,45 for Pernal).
Thanks.
--
Yves-Antoine Emmanuelli.
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
Yves-Antoine Emmanuelli
-
yaemmanuelli - Posts: 16
- Joined: Sat Aug 01, 2020 6:29 pm
- Location: Recloses, France
Re: Databases in Linux
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)"
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)"
-
Morten|Dyalog - Posts: 458
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Databases in Linux
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 :-) :
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.
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
Yves-Antoine Emmanuelli
-
yaemmanuelli - Posts: 16
- Joined: Sat Aug 01, 2020 6:29 pm
- Location: Recloses, France
Re: Databases in Linux
Hello Morten,
Here are the same query executions with isql, a command line query tool shipped with unixodbc:
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.
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
Yves-Antoine Emmanuelli
-
yaemmanuelli - Posts: 16
- Joined: Sat Aug 01, 2020 6:29 pm
- Location: Recloses, France
Re: Databases in Linux
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:
Create Linux users db2inst1 and db2fenc1
Create instance db2inst1:
Post installation actions:
Before starting Dyalog APL with <your preferred user>, execute:
The documentation is there:
https://www.ibm.com/support/knowledgece ... lcome.html
Regards.
--
Yves-Antoine Emmanuelli
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
Yves-Antoine Emmanuelli
-
yaemmanuelli - Posts: 16
- Joined: Sat Aug 01, 2020 6:29 pm
- Location: Recloses, France
Re: Databases in Linux
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
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
-
Morten|Dyalog - Posts: 458
- Joined: Tue Sep 09, 2008 3:52 pm
Re: Databases in Linux
Hello Morten,
I'll report this to a Db2 forum.
Regards.
--
Yves-Antoine Emmanuelli.
I'll report this to a Db2 forum.
Regards.
--
Yves-Antoine Emmanuelli.
--
Yves-Antoine Emmanuelli
Yves-Antoine Emmanuelli
-
yaemmanuelli - Posts: 16
- Joined: Sat Aug 01, 2020 6:29 pm
- Location: Recloses, France
12 posts
• Page 1 of 2 • 1, 2
Who is online
Users browsing this forum: No registered users and 0 guests
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group