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

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

book

Article ID: KB0078591

calendar_today

Updated On:

Products Versions
Spotfire Server All versions

Description

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)

Issue/Introduction

Example log patterns of successful and unsuccessful use of Temporary Tables for Information Links, illustrated using an SAP Hana Data Source

Resolution

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

  1. Executing Information Link
  2. Create Temp Table
  3. Drop Temp Table (just a few milliseconds after the create line)
  4. Executing Information Link failed
when working the pattern is
  1. Executing Information Link
  2. Create Temp Table
  3. Insert into Temp Table
  4. Select with join to Temp table
  5. Drop Temp Table
Executed Information Link Successfully

Additional Information

Documentation: XML settings for data source templates
External: SAP HANA - CREATE TABLE Statement (Data Definition)
Powered by Wolken Software