Python IBM_DB Array Enhancement

[op123singh]

Problem statement:

Above Python programs generates random values as array using random package
arra_data1 ,arra_data2,arra_data3,arra_data4,arra_data5 = [],[],[],[],[]
arra_data6,arra_data7,arra_data8,arra_data9 = [],[],[],[]

Stored procedure INSERTARRSP003 takes array as input and insert into table TABLE_INSERT_BASE1_001 using UNNEST array function.
These array values are passed as input to stored procedure MANOJKU.INSERTARRSP003 using python program with IBM_DB.
try:
stmt = ibm_db.prepare(conn, "CALL MANOJKU.INSERTARRSP003(?, ?, ?, ?, ?, ?, ?, ?, ?)")

          if stmt:
              ibm_db.bind_param(stmt, 1, arra_data1,"array")
              ibm_db.bind_param(stmt, 2, arra_data2,"array")
              ibm_db.bind_param(stmt, 3, arra_data3,"array")
              ibm_db.bind_param(stmt, 4, arra_data4,"array")
              ibm_db.bind_param(stmt, 5, arra_data5,"array")
              ibm_db.bind_param(stmt, 6, arra_data6,"array")
              ibm_db.bind_param(stmt, 7, arra_data7,"array")
              ibm_db.bind_param(stmt, 8, arra_data8,"array")
              ibm_db.bind_param(stmt, 9, arra_data9,"array")
            
              ibm_db.execute(stmt)
            
             logging.info(f"Stored Proc executed and Inserted rows : {ibm_db.num_rows(stmt)} ")

Python programs is getting stuck with ibm_db.execute(stmt) , where it is trying to execute INSERTARRSP003 with array as input .
And it runs for hours without any progress nor abend , as currently IBM_DB does not provide support for array processing.

[op123singh]

22#sp_array-1.txt
INSERTARRSP003-1.txt

[Earammak]

@op123singh, Array support is already provided for ibm_db [ The test suite for the array is available in: https://github.com/ibmdb/python-ibmdb/blob/master/ibm_db_tests/test_alltypesarray.py].
Below is the sample example tried with stored procedure that creates a table and insert into the table which contains 2 columns for a table. It is working fine.

from faker import Faker
crsp = """CREATE OR REPLACE PROCEDURE PROCEDURE (
    IN p_int_value INT,
    IN p_string_value VARCHAR(100)
)
LANGUAGE SQL
BEGIN
     CREATE table simple_table (int_column int, string_column  varchar(50));
    INSERT INTO simple_table (int_column, string_column)
    VALUES (p_int_value, p_string_value);
END
"""
def rand_data_gen(rows):
    fake = Faker()
    return ((fake.random_int(0,500000),fake.random_int(0,9)) for j in range(rows))
data2 = list(rand_data_gen(1))


arra_data1, arra_data2 = [], [] 
for i in range(len(data2)):
    arra_data1.append(data2[i][0])
    arra_data2.append(data2[i][1])

conn=ibm_db.connect(conn_str,'','')
connState = ibm_db.active(conn)
sp_create = ibm_db.exec_immediate(conn, crsp)
stmt = ibm_db.prepare(conn, "CALL ZURBIE.PROCEDURE(?, ?)")
if stmt:
    b1 = ibm_db.bind_param(stmt, 1, arra_data1,"array")
    b2 = ibm_db.bind_param(stmt, 2, arra_data2,"array")	   
    ibm_db.execute(stmt)
    ibm_db.rollback(conn)       
    ibm_db.close(conn)'''

Kindly run the above test program and let me know the result.

Thanks

[Manojkumarbyibm]

@Earammak, above stored procedure is taking one value at a time and does insert of one value at a time. So, say if we have an array of 50 elements, then stored procedure will be called 50 times for 50 elements insertion.

CREATE OR REPLACE PROCEDURE PROCEDURE (
IN p_int_value INT,
IN p_string_value VARCHAR(100)
)
LANGUAGE SQL
BEGIN
CREATE table simple_table (int_column int, string_column varchar(100));
INSERT INTO simple_table (int_column, string_column)
VALUES (p_int_value, p_string_value);
END

But actual requirement is to take all 50 elements as array into db2 z/OS and then unnest and insert to table with single array stored procedure execution. This type of array stored procedure execution is not being supported in python.

Please find example of array stored procedure.

Table Definition:

CREATE TABLE MANOJKU.SIMPLE(INT_COLUMN int, STRING_COLUMN varchar(100));

Array Definition:

CREATE TYPE MANOJKU.INTARRAY
AS INTEGER
ARRAY[50];

CREATE TYPE MANOJKU.VARCHARARRAY2
AS VARCHAR(100) FOR SBCS DATA CCSID EBCDIC
ARRAY[50];

Stored Procedure Definition:

CREATE OR REPLACE PROCEDURE MANOJKU.INSERTER2
(IN INTARRAY MANOJKU.INTARRAY,
IN VARCHARARRAY2 MANOJKU.VARCHARARRAY2)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN

INSERT INTO MANOJKU."SIMPLE"(INT_COLUMN , STRING_COLUMN)
SELECT T.INT_COLUMN , T.STRING_COLUMN
FROM UNNEST(INTARRAY ,
VARCHARARRAY2) AS T(INT_COLUMN , STRING_COLUMN) ;
COMMIT ;

Sample Program:

from faker import Faker
import ibm_db

crsp1 = """CREATE TYPE MANOJKU.INTARRAY
AS INTEGER
ARRAY[50];
"""

crsp2 = """CREATE TYPE MANOJKU.VARCHARARRAY2
AS VARCHAR(100) FOR SBCS DATA CCSID EBCDIC
ARRAY[50];
COMMIT;
"""

crsp3 = """CREATE OR REPLACE PROCEDURE MANOJKU.INSERTER2
(IN INTARRAY MANOJKU.INTARRAY,
IN VARCHARARRAY2 MANOJKU.VARCHARARRAY2)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
CREATE TABLE MANOJKU.SIMPLE(INT_COLUMN int, STRING_COLUMN varchar(100));

INSERT INTO MANOJKU."SIMPLE"(INT_COLUMN , STRING_COLUMN)
SELECT T.INT_COLUMN , T.STRING_COLUMN
FROM UNNEST(INTARRAY ,
VARCHARARRAY2) AS T(INT_COLUMN , STRING_COLUMN) ;
COMMIT ;
"""

def rand_data_gen(rows):
fake = Faker()
return ((fake.random_int(0,500000),fake.random_int(0,9)) for j in range(rows))

data2 = list(rand_data_gen(100))

arra_data1, arra_data2 = [], []
for i in range(len(data2)):
arra_data1.append(data2[i][0])
arra_data2.append(data2[i][1])

conn=ibm_db.connect(conn_str,'','')
connState = ibm_db.active(conn)

create1 = ibm_db.exec_immediate(conn, crsp1)
create2 = ibm_db.exec_immediate(conn, crsp2)
sp_create = ibm_db.exec_immediate(conn, crsp3)

stmt = ibm_db.prepare(conn, "CALL MANOJKU.INSERTER2(?, ?)")
if stmt:
b1 = ibm_db.bind_param(stmt, 1, arra_data1,"array")
b2 = ibm_db.bind_param(stmt, 2, arra_data2,"array")
ibm_db.execute(stmt)
ibm_db.rollback(conn)
ibm_db.close(conn)

Kindly please let me know, if you need any additional information.

Regards,
Manoj

[Dutch58]

Dozens of variations fail to execute my call to 'stmt' so when I saw this I thought I would try again......but using this very different format just gives the never-ending msg given by a dozen other attempted formats of a call to an SP:
b1 = ibm_db.bind_param(stmt, 1, hp_id,"string")
Exception: Describe Param Failed: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=HYC00 SQLCODE=-99999

My Script after trying to mirror the advice given but my parms are not arrays of course: (watch for comments, it's a clone)

import os

os.add_dll_directory("C:\HighmarkApps\Python3.12.2\Lib\site-packages\clidriver\bin")

os.add_dll_directory("C:\HighmarkApps\Python3.12.2\Lib\site-packages\clidriver\bin\amd64.VC12.CRT")
os.add_dll_directory("C:\HighmarkApps\Python3.12.2\Lib\site-packages\clidriver\bin\icc64")
os.add_dll_directory("C:\ProgramData\IBM\DB2\C_Highmarkapps_Python3.12.2_Lib_site-packages_clidriver\cfg")
os.environ['IBM_DB_HOME'] = "C:\HighmarkApps\Python3.12.2\Lib\site-packages\clidriver\bin\db2cli.exe"
#os.environ['IBM_DB_HOME'] = "C:\ProgramData\IBM\DB2\C_Highmarkapps_Python3.12.2_Lib_site-packages_clidriver\cfg"
#IBM_DB_HOME=
#LIB=$IBM_DB_HOME/lib:$LIB

import sys
sys.path.append('C:\HighmarkApps\Python3.12.2\Lib\site-packages\clidriver\bin\amd64.VC12.CRT')
sys.path.append('C:\HighmarkApps\Python3.12.2\Lib\site-packages\clidriver\bin\icc64')
sys.path.append('C:\ProgramData\IBM\DB2\C_Highmarkapps_Python3.12.2_Lib_site-packages_clidriver\cfg')
sys.path.append('IBM_DB_HOME')

for p in sys.path:
print(p)

import ibm_db
#import ibm_db_dbi

conn_string = "DSN=DB2TVIPA;UID=LIDDVDP;PWD=Karjulkenhedstv20;DATABASE=DB2TVIPA;HOSTNAME=DB2TVIPA;PORT=447;PROTOCOL=TCPIP;SECURITY=SOCKETS;AUTHENTICATION=SERVER;CURRENTSCHEMA=DB2TEST;"

conn = ibm_db.connect(conn_string, "", "")

if not conn:
print ('connection failed')
sys.exit()

hp_id = "000923196"
user_id = "LIDDVDP"
first_string = " "
first_code = 0
second_string = " "
second_code = 0
reason = "-"

print ("Values of parameters before CALL:")

print(hp_id, user_id, first_code, first_string, second_code, second_string, reason)

params = (hp_id, user_id, first_code, first_string, second_code, second_string)

#SET SCHEMA DB2TVIPA.DB2TEST;
ibm_db.exec_immediate(conn, "SET SCHEMA DB2TEST")

ibm_db.callproc(conn,"DB2TEST.SP_DEN_PRV_GET_PROVIDER_PRACTICE_NAME", params)

stmt = ibm_db.prepare(conn, "CALL DB2TEST.SP_DEN_PRV_GET_PROVIDER_PRACTICE_NAME(?, ?, ?, ?, ?, ?)")
if stmt:
b1 = ibm_db.bind_param(stmt, 1, hp_id,"string")
b2 = ibm_db.bind_param(stmt, 2, user_id,"string")
b3 = ibm_db.bind_param(stmt, 3, first_code,"integer")
b4 = ibm_db.bind_param(stmt, 4, first_string,"string")
b5 = ibm_db.bind_param(stmt, 5, second_code,"integer")
b6 = ibm_db.bind_param(stmt, 6, second_string,"string")
ibm_db.execute(stmt)

print(ibm_db.fetch_assoc(stmt))
row = ibm_db.fetch_assoc(stmt)
while row:
    print(row["PARAMETER"])
    print(row["VALUE"])
    row = ibm_db.fetch_assoc(stmt)

ibm_db.close(conn)

[imavo]

@Dutch58 you should create a separate new issue for your symptom because your question is not about arrays.

After you create a new issue, Please follow the instructions below to upload tracefiles that will allow us to see what is really happening.

On your ms-windows workstation, open a cmd shell with "Run as administrator", and in that window run the commands below (change to use your real script name, instead of your_script.py):

Ensure that db2trc.exe is on your PATH, and if not, then use SET PATH=%PATH%;... , where ... is your fully qualified path to the clidriver bin directory. This temporarily appends the clidriver\bin directory to your PATH

db2trc on -f %temp%\cli0150e.dmp
python your_script.py
db2trc off
db2trc fmt -cli %temp%\cli0150e.dmp %temp%\cli0150e.fmtcli
db2trc fmt %temp%\cli0150e.dmp %temp%\cli0150e.fmt
db2trc flw %temp%\cli0150e.dmp %temp%\cli0150e.flw

Now upload/attach those 3 files to your newly created issue, that is: %temp%\cli0150e.fmtcli , %temp%\cli0150e.fmt, %temp%\cli0150e.flw
or make this files available on some website with a link from which we can download them to investigate.