Product: TIBCO Spotfire®
Not able to load on-demand Information Links for Teradata - "Error running DROP TABLE"
When using an Information Link for Teradata as an on-demand data table in an analysis, you may see that it fails when passing a lot of unique values to it. In the server.log file, you can see the following error:
ERROR 2017-09-13T14:07:13,639-0700 [*pool-13-thread-1, admin, #1, #82*] ds.sql.SQLQuerySession: Error running DROP TABLE SFTMP33683165153
java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.03] [Error 3807] [SQLState 42S02] Object 'SFTMP33683165153' does not exist.
at com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:309) ~[terajdbc4.jar:16.10.00.03]
at com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:103) ~[terajdbc4.jar:16.10.00.03]
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:311) ~[terajdbc4.jar:16.10.00.03]
at com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:200) ~[terajdbc4.jar:16.10.00.03]
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:137) ~[terajdbc4.jar:16.10.00.03]
at com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:128) ~[terajdbc4.jar:16.10.00.03]
at com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:389) ~[terajdbc4.jar:16.10.00.03
com.spotfire.ws.dat.DatException: com.spotfire.ws.dat.OperatorException: com.spotfire.ws.im.IMException: Failed to execute query: [Teradata Database] [TeraJDBC 16.10.00.03] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.
at com.spotfire.ws.dat.Formatter.open(Formatter.java:441) ~[server-is.jar:?]
at com.spotfire.ws.dat.Job$OpenTask.execute(Job.java:831) ~[server-is.jar:?]
at com.spotfire.ws.dat.Job$Task.run(Job.java:753) ~[server-is.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_131]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]
Caused by: com.spotfire.ws.dat.OperatorException: com.spotfire.ws.im.IMException: Failed to execute query: [Teradata Database] [TeraJDBC 16.10.00.03] [Error 3932] [SQLState 25000] Only an ET or null statement is legal after a DDL Statement.
To resolve the issue follow the steps below.
1). Open the configuration tool for the installed TIBCO Spotfire Server.
2). Enter the password when prompted.
3). Go to the configuration tab.
4). Choose Data-Source Templates.
5). Mark the Teradata template so thqt you can see its contents.
6). Change the following:
<create-temp-table-command>CREATE GLOBAL TEMPORARY TABLE $$name$$ $$column_list$$ ON COMMIT PRESERVE ROWS</create-temp-table-command> <drop-temp-table-command>TRUNCATE TABLE $$name$$;DROP TABLE $$name$$</drop-temp-table-command>
To the following:
<create-temp-table-command>CREATE VOLATILE TABLE $$name$$ $$column_list$$ ON COMMIT PRESERVE ROWS; ET; /create-temp-table-command> <drop-temp-table-command>DROP TABLE $$name$$</drop-temp-table-command>
7). Save configuration to a database.
8). Restart the TIBCO Spotfire Server.
This will resolve the issues on any new data source elements created using this template. To ensure that already existing data source elements are updated, you need to go into Information Designer and edit and re-save all existing data source elements that uses this template.
Comments
0 comments
Article is closed for comments.