Example log patterns of successful and unsuccessful use of Temporary Tables for Information Links – Revvity Signals

Date Posted: 2019-04-29
Product: TIBCO Spotfire®

Problem:

Example log patterns of successful and unsuccessful use of Temporary Tables for Information Links

Solution:

When loading data from Information Links using On-Demand, for large markings (>10000 rows by default), temporary tables will be used instead of large WHERE clauses.

The default command to create the temporary tables is (line as seen in Data Source template):

<create-temp-table-command>CREATE TEMPORARY TABLE $$name$$ $$column_list$$</create-temp-table-command>

Note 1: Refer to the Data Source vendor documentation for details on the exact syntax, which is specific for each Data Source (and it may have options better suited for temp tables).
Note 2: If the command used fails, you will not get any data (since the Information Link will fail its execution).

The following is an example of a failing temp table creation, as seen in the sql.log on the TIBCO Spotfire Server:

2019-04-24T10:26:12,508+0200 [*pool-14-thread-1, Administrator@nfalk.local, #0, #93*]: Executing Information Link 'V_SUPERSTORESALES':
Columns:
RECORD_NO (dbe3ee6e-5cd9-495f-943e-232a439e28c9)
ORDER_PRIORITY (c0529854-5655-4fd1-aaf7-940710cd0f22)
SALES_TOTAL (86cb6092-816d-4a20-a99f-80d956e3c9cb)
DISCOUNT (2988067a-e3a9-491b-aef0-3232cc1783a7)
SHIP_METHOD (6e278c26-060f-46e5-908e-0ec3ecd1c651)
DELIVERY_TIME (6d7961aa-7e84-4dd0-b15e-76a6e9c52ae4)
GROSS_PROFIT (fe507714-bcd2-483a-84ce-e3f239a26d41)
CUSTOMER_NAME (b040c976-ea9b-482c-a791-d03a1d575803)
REGION (f2fd9f35-0263-484b-ab79-d3e953278f2d)
STATE (84496d83-d3ff-43e2-9642-77cd4ef84f3f)
MARKET_SEGMENT (05f584ff-2d65-432e-be9b-c8c227f4666a)
PRODUCT_CATEGORY_1 (1aa907f3-53a8-4155-a78a-57ab6c7af868)
PRODUCT_CATEGORY_2 (70fbfec8-8dd6-448c-a94f-4583bf13a083)
PRODUCT_CATEGORY_3 (7add0ec3-d4c1-481d-bc93-229e235009f2)
PRODUCT_CATETORY_4 (2971ba43-f1a6-4112-8c3d-a5611ec11653)
SHIP_CONTAINER (f0793fea-3fbc-478b-af33-5eedae63c840)
ORDER_ID (208c0f60-2e27-4f01-887d-8ce4f56c72d8)
ORDER_DATE (b17ffe40-a5ee-40ce-98ab-caf771dd80c0)
DELIVERY_DATE (84ad9baf-889a-468d-938c-849e72ed3bad)
PRODUCT_INVENTORY (e6e8ce4c-7d77-4a8d-aa1a-4169cd1ba5a5)

2019-04-24T10:26:12,539+0200 [*pool-14-thread-1, Administrator@nfalk.local, #0, #93*]: [data-source="hanaserver"] CREATE TEMPORARY TABLE SFTMP943726972 ("VALUE" INTEGER NULL)
2019-04-24T10:26:12,586+0200 [*pool-14-thread-1, Administrator@nfalk.local, #0, #93*]: [data-source="hanaserver"] DROP TABLE SFTMP943726972
2019-04-24T10:26:13,383+0200 [*Thread-27, Administrator@nfalk.local, #0, #93*]: Information Link 'V_SUPERSTORESALES' execution failed, time: 0.875 seconds

In the server.log, these ERRORs can be seen (it is not expecting "TEMPORARY" after "CREATE ", it expects "LOCAL TEMPORARY" or "GLOBAL TEMPORARY"):

ERROR 2019-04-24T10:26:12,586+0200 [*pool-14-thread-1, Administrator@nfalk.local, #0, #93*] ds.sql.SQLQuerySession: Error running DROP TABLE SFTMP943726972
com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [259] (at 12): invalid table name: SFTMP943726972: line 1 col 13 (at pos 12)
...
ERROR 2019-04-24T10:26:13,383+0200 [*pool-14-thread-1, Administrator@nfalk.local, #0, #93*] ws.dat.AbstractOperator: Start job failed.
com.spotfire.ws.dat.OperatorException: com.spotfire.ws.im.IMException: Failed to execute query: SAP DBTech JDBC: [257] (at 8): sql syntax error: incorrect syntax near "TEMPORARY": line 1 col 8 (at pos 8)

For the specific example here, using SAP Hana, for this data source type there are two major options, LOCAL and GLOBAL temporary tables, and LOCAL seems more appropriate since they are tied to the session.
But LOCAL temporary tables needs to have # as prefix so the addition to the Data Source template needs to be:

<temp-table-name-pattern>#$$name$$</temp-table-name-pattern>
<create-temp-table-command>CREATE LOCAL TEMPORARY TABLE $$name$$ $$column_list$$;</create-temp-table-command>

After correcting the template, restarting the Spotfire Server and re-saving the Data Source using this template, the log pattern becomes the following:

2019-04-24T10:30:00,080+0200 [*pool-14-thread-1, Administrator@nfalk.local, #2, #178*]: Executing Information Link 
'V_SUPERSTORESALES': 
Columns: 
RECORD_NO (dbe3ee6e-5cd9-495f-943e-232a439e28c9) 
ORDER_PRIORITY (c0529854-5655-4fd1-aaf7-940710cd0f22) 
SALES_TOTAL (86cb6092-816d-4a20-a99f-80d956e3c9cb) 
DISCOUNT (2988067a-e3a9-491b-aef0-3232cc1783a7) 
SHIP_METHOD (6e278c26-060f-46e5-908e-0ec3ecd1c651) 
DELIVERY_TIME (6d7961aa-7e84-4dd0-b15e-76a6e9c52ae4) 
GROSS_PROFIT (fe507714-bcd2-483a-84ce-e3f239a26d41) 
CUSTOMER_NAME (b040c976-ea9b-482c-a791-d03a1d575803) 
REGION (f2fd9f35-0263-484b-ab79-d3e953278f2d) 
STATE (84496d83-d3ff-43e2-9642-77cd4ef84f3f) 
MARKET_SEGMENT (05f584ff-2d65-432e-be9b-c8c227f4666a) 
PRODUCT_CATEGORY_1 (1aa907f3-53a8-4155-a78a-57ab6c7af868) 
PRODUCT_CATEGORY_2 (70fbfec8-8dd6-448c-a94f-4583bf13a083) 
PRODUCT_CATEGORY_3 (7add0ec3-d4c1-481d-bc93-229e235009f2) 
PRODUCT_CATETORY_4 (2971ba43-f1a6-4112-8c3d-a5611ec11653) 
SHIP_CONTAINER (f0793fea-3fbc-478b-af33-5eedae63c840) 
ORDER_ID (208c0f60-2e27-4f01-887d-8ce4f56c72d8) 
ORDER_DATE (b17ffe40-a5ee-40ce-98ab-caf771dd80c0) 
DELIVERY_DATE (84ad9baf-889a-468d-938c-849e72ed3bad) 
PRODUCT_INVENTORY (e6e8ce4c-7d77-4a8d-aa1a-4169cd1ba5a5) 

2019-04-24T10:30:00,126+0200 [*pool-14-thread-1, Administrator@nfalk.local, #2, #178*]: [data-source="hanaserver"] 
CREATE LOCAL TEMPORARY TABLE #SFTMP945993436 ("VALUE" INTEGER NULL) 
2019-04-24T10:30:00,158+0200 [*pool-14-thread-1, Administrator@nfalk.local, #2, #178*]: [data-source="hanaserver"] 
INSERT INTO #SFTMP945993436 VALUES (10000) 
INSERT INTO #SFTMP945993436 VALUES (10001) 
INSERT INTO #SFTMP945993436 VALUES (10002) 
INSERT INTO #SFTMP945993436 VALUES (10003) 
INSERT INTO #SFTMP945993436 VALUES (10004) 
INSERT INTO #SFTMP945993436 VALUES (10005) 
INSERT INTO #SFTMP945993436 VALUES (10006) 
INSERT INTO #SFTMP945993436 VALUES (10007) 
INSERT INTO #SFTMP945993436 VALUES (10008) 
INSERT INTO #SFTMP945993436 VALUES (10009) 
... 

2019-04-24T10:30:01,080+0200 [*pool-14-thread-1, Administrator@nfalk.local, #2, #178*]: [data-source="hanaserver"] 
SELECT 
V1."RECORD_NO" AS "RECORDNO", 
V1."ORDER_PRIORITY" AS "ORDERPRIORITY", 
V1."SALES_TOTAL" AS "SALESTOTAL", 
V1."DISCOUNT" AS "DISCOUNT", 
V1."SHIP_METHOD" AS "SHIPMETHOD", 
V1."DELIVERY_TIME" AS "DELIVERYTIME", 
V1."GROSS_PROFIT" AS "GROSSPROFIT", 
V1."CUSTOMER_NAME" AS "CUSTOMERNAME", 
V1."REGION" AS "REGION", V1."STATE" AS "STATE", 
V1."MARKET_SEGMENT" AS "MARKETSEGMENT", 
V1."PRODUCT_CATEGORY_1" AS "PRODUCTCATEGORY1", 
V1."PRODUCT_CATEGORY_2" AS "PRODUCTCATEGORY2", 
V1."PRODUCT_CATEGORY_3" AS "PRODUCTCATEGORY3", 
V1."PRODUCT_CATETORY_4" AS "PRODUCTCATETORY4", 
V1."SHIP_CONTAINER" AS "SHIPCONTAINER", 
V1."ORDER_ID" AS "ORDERID", 
V1."ORDER_DATE" AS "ORDERDATE", 
V1."DELIVERY_DATE" AS "DELIVERYDATE", 
V1."PRODUCT_INVENTORY" AS "PRODUCTINVENTORY" 
FROM "SUPERSTORESALES"."V_SUPERSTORESALES" V1, 
#SFTMP945993436 A2 
WHERE (V1."ORDER_ID" = A2."VALUE") 

2019-04-24T10:30:02,455+0200 [*pool-14-thread-2, Administrator@nfalk.local, #2, #178*]: [data-source="hanaserver"] DROP TABLE #SFTMP945993436 
2019-04-24T10:30:02,486+0200 [*pool-14-thread-2, Administrator@nfalk.local, #2, #178*]: Information Link 'V_SUPERSTORESALES' executed successfully, time: 2.406 seconds 
2019-04-24T10:30:02,830+0200 [*Thread-27, Administrator@nfalk.local, #2, #178*]: Information Link 'V_SUPERSTORESALES' job closed, time: 2.75 seconds

In short, when failing the pattern is

Executing Information Link
Create Temp Table
Drop Temp Table (just a few milliseconds after the create line)
Executing Information Link failed

when working the pattern is

Executing Information Link
Create Temp Table
Insert into Temp Table
Select with join to Temp table
Drop Temp Table

Executed Information Link Successfully Documentation: XML settings for data source templates

https://docs.tibco.com/pub/spotfire_server/10.2.0/doc/html/TIB_sfire_server_tsas_admin_help/GUID-1BE3A384-25F2-4CA1-966E-534B39239CD0.html

External: SAP HANA - CREATE TABLE Statement (Data Definition)

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/20d58a5f75191014b2fe92141b7df228.html

Related articles