Relacing a word in an db2 sql file causes DSNC105I : End of file reached while reading the command error

Question

I have a dynamic sql file in which name of TBCREATOR changes as given in a parameter.

I use a simple python script to change the TBCREATOR=<variable here> and write the result to an output sql file. calling this file using db2 -td@ -vf <generated sql file>gives DSNC105I : End of file reached while reading the command

Here is the file i need the TBCREATOR variable replaced:

CONNECT to 204.90.115.200:5040/DALLASC user *** using ****@
select REMARKS from sysibm.SYSCOLUMNS WHERE TBCREATOR='table' AND NAME='LCODE'
@

Here is the python script:

#!/usr/bin/python3

# #------replace table value with schema name
# print(list_of_lines)
fin = open("decrypt.sql", "rt")
#output file to write the result to
fout = open("decryptout.sql", "wt")
for line in fin:
    fout.write(line.replace('table', 'ZXP214'))

fin.close()
fout.close()

After decryptout.sql is generated I call it using db2 -td@ -vf decryptout.sql and get the error given above.

Whats irritating is I have another sql file that contains exactly same data as decryptout.sql which runs smoothly with the db2 -td@ -vf ... command. I tried to use the unix command cmp to compare the generated file and the one which I wrote, with the variable ZXP214 already replaced but there are no differences. What is causing this error?.

here is the file (that executes without error) I compare generated output with:

CONNECT to 204.90.115.200:5040/DALLASC user *** using ****@
select REMARKS from sysibm.SYSCOLUMNS WHERE TBCREATOR='ZXP214' AND NAME='LCODE'
@

Answer 1

If you are using the Db2-LUW CLP (command line processor) that is written in c/c++ and runs on windows/linux/unix, then your syntax for CONNECT is not valid.

Unfortunately your question is ambigiously tagged so we cannot tell which Db2-server platform you actually use.

For Db2-LUW with the c/c++ written classic db2 command, the syntax for a type-1 CONNECT statement does not allow a connection-string (or partial connection string) as shown in your question. For Db2-LUW db2 clp, the target database must be externally defined (i.e not inside the script) , either via the legacy actions of both catalog tcpip node... combined with catalog database..., or must be defined in the db2dsdriver.cfg configuration file as plain XML.

If you want to use connection-strings then you can use the clpplus tool which is available for some Db2-LUW client packages, and is present on currently supported Db2-LUW servers. This lets you use Oracle style scripting with Db2. Refer to the online documentation for details.

If you not using the c/c++ classic db2 command, and you are instead using the emulated clp written in java only available with Z/OS-USS, then you must open a ticket with IBM support for that component, as that is not a matter for stackoverflow.

Answer 2

I found that specifically on the https://ibmzxplore.influitive.com/ challenge, if you are using the java db2 command and working in the Zowe USS system (Unix System Services of zOS), there is a conflict of character sets. I believe the system will generally create files in EBCDIC format, whereas if you do

echo "CONNECT ..." > syscat.clp

the resulting file will be tagged as ISO8859-1 and will not be processed properly by db2. Instead, go to the USS interface and choose "create file", give it a folder and a name, and it will create the file untagged. You can use

ls -T

to see the tags. Then edit the file to give it the commands you need, and db2 will interoperate with it properly. Because you are creating the file with python, you may be running into similar issues. When you open the new file, use something like

open(input_file_name, mode=”w”, encoding=”cp1047”)

This makes sure the file is open as an EBCDIC file.