pydb2-discuss

[PyDB2-discuss] execute fails on export

[Brent S. E. <we...@ai...>]

Can pydb2 handle an export command in an execute statement?

When I use the execute statement on an export command I get the
following error:

Error('42601', -104, '[IBM][CLI Driver][DB2/AIX64] SQL0104N  An
unexpected token "EXPORT TO /home/brente/supply_load/MDCP_TES" was found
following "BEGIN-OF-STATEMENT".  Expected tokens may include:
"<space>".  SQLSTATE=42601\n')

Here is the sql statement I am trying to execute:

EXPORT TO /home/brente/supply_load/MDCP_TEST/ALLOC.PREV.3 OF DEL SELECT
* FROM ATPGUI.WORK_ALLOC_TAB WHERE WORKING = 'PREVIOUS' FOR READ ONLY


If I run the export statement directly in db2 from the command line it
works.  I have no problems with select statements in the execute
command.  I thought execute was supposed to execute any valid sql.

Any ideas?

Re: [PyDB2-discuss] execute fails on export

[<len...@la...>]

> Can pydb2 handle an export command in an execute statement?

export is not part of sql so that wont work. You don't mention which
version of db2 you are running, but at least for 9.5 you should be able to
run export via the admin_cmd procedure:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0012547.html?resultof=%22%61%64%6d%69%6e%5f%63%6d%64%22%20

Not sure of earlier versions, and I haven't tried it myself though.

HTH
/Lennart

[...]




>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> PyDB2-discuss mailing list
> PyD...@li...
> https://lists.sourceforge.net/lists/listinfo/pydb2-discuss
>

Re: [PyDB2-discuss] execute fails on export

[Brent S. E. <we...@ai...>]

So how do I use the admin_cmd procedure from pydb2?
I tried import DB2
conn = DB2.connect(...
curs = conn.cursor()

Then I assume curs.execute(something).
But what would the exact syntax be to try admin_cmd on the following
statement?

export to /home/brente/file.del of del select * from schema.table where
working = 'PREVIOUS' for read only

My db2 client is 8.2 and the db2 server I am connecting to is 9.2.

On Sun, 2009-04-05 at 17:47 +0200, len...@la... wrote:
> 
> > Can pydb2 handle an export command in an execute statement?
> 
> export is not part of sql so that wont work. You don't mention which
> version of db2 you are running, but at least for 9.5 you should be able to
> run export via the admin_cmd procedure:
> 
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.rtn.doc/doc/r0012547.html?resultof=%22%61%64%6d%69%6e%5f%63%6d%64%22%20
> 
> Not sure of earlier versions, and I haven't tried it myself though.
> 
> HTH
> /Lennart
> 
> [...]
> 
> 
> 
> 
> >
> >
> >
> > ------------------------------------------------------------------------------
> > _______________________________________________
> > PyDB2-discuss mailing list
> > PyD...@li...
> > https://lists.sourceforge.net/lists/listinfo/pydb2-discuss
> >
> 

Re: [PyDB2-discuss] execute fails on export

[<len...@la...>]

> So how do I use the admin_cmd procedure from pydb2?

Never tried it myself so I made up a rather silly example. Note how one
have to quote string literals inside the sql string sent to admin_cmd:

[db2inst1@wbv9 ~]$ rm -f /tmp/TT.ixf
[db2inst1@wbv9 ~]$ cat admin_cmd_example.py
#!/usr/bin/python

import DB2
import sys

dbname = sys.argv[1]
username = sys.argv[2]
passwd   = sys.argv[3]

conn = DB2.connect(dsn=dbname, uid=username, pwd=passwd)

c1 = conn.cursor()
c1.execute("call sysproc.admin_cmd('export to /tmp/TT.ixf of ixf select x
from lateral(values (''a'')) T (x)')")
for row in c1.fetchall():
    print row


[db2inst1@wbv9 ~]$ ./admin_cmd_example.py <yourdb> <youruser> <yourpasswd>
(1L, None, None)
[db2inst1@wbv9 ~]$ ls -l /tmp/TT.ixf
-rw-r--r--  1 db2fenc1 db2fadm1 2768 Apr  6 16:57 /tmp/TT.ixf


HTH
/Lennart

Re: [PyDB2-discuss] execute fails on export

[Brent S. E. <we...@ai...>]

It looks to me like the file that the command writes to is on the server
machine.  Whereas when I just run the export command from the command
line I can write to my client machine.  The only access I have to the
server machine is db2 access.  So, the only directory I can write to
using the admin_cmd is /tmp on the server which doesn't do me much good.
I have changed my python code to fetch all of the records and then
manually write the data to the file where I want it on the client
machine.

Thanks,
Brent

On Mon, 2009-04-06 at 17:02 +0200, len...@la... wrote:
> > So how do I use the admin_cmd procedure from pydb2?
> 
> Never tried it myself so I made up a rather silly example. Note how one
> have to quote string literals inside the sql string sent to admin_cmd:
> 
> [db2inst1@wbv9 ~]$ rm -f /tmp/TT.ixf
> [db2inst1@wbv9 ~]$ cat admin_cmd_example.py
> #!/usr/bin/python
> 
> import DB2
> import sys
> 
> dbname = sys.argv[1]
> username = sys.argv[2]
> passwd   = sys.argv[3]
> 
> conn = DB2.connect(dsn=dbname, uid=username, pwd=passwd)
> 
> c1 = conn.cursor()
> c1.execute("call sysproc.admin_cmd('export to /tmp/TT.ixf of ixf select x
> from lateral(values (''a'')) T (x)')")
> for row in c1.fetchall():
>     print row
> 
> 
> [db2inst1@wbv9 ~]$ ./admin_cmd_example.py <yourdb> <youruser> <yourpasswd>
> (1L, None, None)
> [db2inst1@wbv9 ~]$ ls -l /tmp/TT.ixf
> -rw-r--r--  1 db2fenc1 db2fadm1 2768 Apr  6 16:57 /tmp/TT.ixf
> 
> 
> HTH
> /Lennart
> 
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> PyDB2-discuss mailing list
> PyD...@li...
> https://lists.sourceforge.net/lists/listinfo/pydb2-discuss