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