DB2 truncate()

228 views
Skip to first unread message

Auden RovelleQuartz

unread,
May 6, 2016, 4:02:18 PM5/6/16
to web2py-users
when i attempt to do a db.tablename.truncate() on a DB2 table, I get the following error:

Ticket ID

127.0.0.1.2016-05-06.14-57-20.5ee10f85-fb6e-4bfe-8d16-db0b224313b9

<class 'pyodbc.ProgrammingError'> ('42000', '[42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token TRUNCATE was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER. (-104) (SQLExecDirectW)')

Version

web2py™Version 2.14.5-stable+timestamp.2016.04.14.03.26.16
PythonPython 2.7.9: C:\Users\arovellequartz\Desktop\web2py_win\web2py\web2py.exe (prefix: )

Traceback

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
Traceback (most recent call last):
  File "C:\Users\arovellequartz\Desktop\web2py_win\web2py\gluon\restricted.py", line 227, in restricted
    exec ccode in environment
  File "C:/Users/arovellequartz/Desktop/web2py_win/web2py/applications/test/controllers/default.py", line 57, in <module>
  File "C:\Users\arovellequartz\Desktop\web2py_win\web2py\gluon\globals.py", line 417, in <lambda>
    self._caller = lambda f: f()
  File "C:/Users/arovellequartz/Desktop/web2py_win/web2py/applications/test/controllers/default.py", line 52, in user
    db.riqtemp26.truncate()
  File "C:\Users\arovellequartz\Desktop\web2py_win\web2py\gluon\packages\dal\pydal\objects.py", line 838, in truncate
    return self._db._adapter.truncate(self, mode)
  File "C:\Users\arovellequartz\Desktop\web2py_win\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1004, in truncate
    self.execute(query)
  File "C:\Users\arovellequartz\Desktop\web2py_win\web2py\gluon\packages\dal\pydal\adapters\db2.py", line 98, in execute
    return self.log_execute(command)
  File "C:\Users\arovellequartz\Desktop\web2py_win\web2py\gluon\packages\dal\pydal\adapters\base.py", line 1382, in log_execute
    ret = self.get_cursor().execute(command, *a[1:], **b)
ProgrammingError: ('42000', '[42000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token TRUNCATE was not valid. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER. (-104) (SQLExecDirectW)')

Is there something special required to use the truncate() feature with DB2 databases?

DenesL

unread,
May 9, 2016, 8:20:40 PM5/9/16
to web2py-users
There are different platforms on which DB2 runs and in some the TRUNCATE command is supported, but that is not the case for iSeries DB2.
Which version of OS are you running?.

The alternative seems to be a DELETE without a WHERE clause which could be set as a TRUNCATE override in another db2 class adapter.
You can use the mssql one as a reference.

Or CLRPFM.

Denes

Auden RovelleQuartz

unread,
May 25, 2016, 10:30:31 AM5/25/16
to web2py-users
OK, yes - - - at the company I am working at we do use iSeries to connect windows desktop to DB2 on AS400 so that would explain why it did not work - I had already found a workaround - thanks for the reply
Reply all
Reply to author
Forward
0 new messages