LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  DB nodes sharing the same database session

    Employee
    Posted 07-03-2012 21:24

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Adam Williamson

    Hello,

    I am wanting to extract a large volume of data from a teradata database.
    Selecting directly from the table is not an option due to spool space limitations.
    Inserting records into a table and joining them later is not an option due to space restrictions.

    One option that works well in Teradata SQL assistant is running the below three statements in the same session.

    Is there anyway Lavastorm can keep the session open across nodes?.
    CREATE VOLATILE TABLE TEMP_TABLE_NAME AS (
SELECT 
COL1
,COL2
,COL3
FROM DATABASE_NAME.TABLE_NAME1
) WITH DATA
PRIMARY INDEX (COL1)
ON COMMIT PRESERVE ROWS;

COLLECT STATS ON TEMP_TABLE_NAME INDEX (COL1);

SELECT
T2.NEW_COL
,T1.COL1
,T1.COL2
,T1.COL3
FROM   
TEMP_TABLE_NAME  T1
JOIN DATABASE_NAME.TABLE_NAME2 T2  ON     T1.COL1 = T2.NEW_COL;


  • 2.  RE: DB nodes sharing the same database session

    Employee
    Posted 07-05-2012 07:03

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: rboccuzzi

    you should be able to use a DB Execute node, and just have 3 statements in the Script box, something like:

    sqlNonSelect("CREATE VOLATILE TABLE TEMP_TABLE_NAME AS (SELECT COL1,COL2,COL3 FROM DATABASE_NAME.TABLE_NAME1) WITH DATA PRIMARY INDEX (COL1) ON COMMIT PRESERVE ROWS")
    sqlNonSelect("COLLECT STATS ON TEMP_TABLE_NAME INDEX (COL1)")
    sqlSelect(1, "SELECTT2.NEW_COL,T1.COL1,T1.COL2,T1.COL3 FROM TEMP_TABLE_NAME T1 JOIN DATABASE_NAME.TABLE_NAME2 T2 ON T1.COL1 = T2.NEW_COL")

    if you create the output pin on the node, you should be all set, this should share the session

    Cheers
    Rich


  • 3.  RE: DB nodes sharing the same database session

    Employee
    Posted 08-20-2012 17:52

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Adam Williamson

    Hi Rich,

    I have been working with Mario Ermacora on this one and still no luck, I have sent the bug report to Mario to help resolve the issue.
    It would be great if Lavastorm could share database sessions accross multiple DB nodes (maybe via clocks?).

    Cheers,
    Adam


  • 4.  RE: DB nodes sharing the same database session

    Employee
    Posted 08-22-2012 08:35

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: rboccuzzi

    What did you have the commit frequency set to? If it was not set, or set to 1, can you set it to 1000 and see if that makes a difference?

    Cheers
    Rich


  • 5.  RE: DB nodes sharing the same database session

    Employee
    Posted 08-23-2012 18:56

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Adam Williamson

    Still no luck, getting the same error where the volatile table is not recognised.

    Cheers,
    Adam


  • 6.  RE: DB nodes sharing the same database session

    Employee
    Posted 09-04-2012 09:00

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: rabbott

    Please report this issue to Technical Support

    Thank you.



  • 7.  RE: DB nodes sharing the same database session

    Employee
    Posted 09-30-2012 23:59

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mermacora

    Finally got around to submitting this in our bug tracking system - issue# 4215.

    Thanks,
    Mario


  • 8.  RE: DB nodes sharing the same database session

    Employee
    Posted 10-19-2012 10:50

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: matt

    After looking at this, the problem is not with session maintenance, it turns out the problem is due to a feature for pre-fetching the metadata for sqlSelect operators. If the SQL statement passed to the sqlSelect operator is a constant string, then the sqlSelect operator tries to pre-fetch the metadata for the query. This enables the sqlSelect statement to produce valid metadata on the output pin even in the case when the query is never actually executed, i.e. a zero input row scenario.

    The problem arises because the table being selected from is a volatile temporary table that does not exist until the statements are executed, therefore it is throwing an error when trying to pre-fetch the metadata. The simple work-around is to make the select statement not a string constant.

    So rewriting the statement from:

    sqlSelect(1, "SQL....")

    TO:

    sqlSelect(1, ""+"SQL...")

    Will solve the problem.

    A bug will be filed to allow for configuration of the pre-fetch metadata behavior.

    As a note, Teradata requires a commit after a DDL statement, so you'll either need to do an explicit commit i.e. sqlNonSelect("COMMIT") or set the CommitFrequency to 1.


    Matt


  • 9.  RE: DB nodes sharing the same database session

    Employee
    Posted 10-22-2012 15:57

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Adam Williamson

    Works a treat!
    Big thanks to everyone who helped on this, great find Matt.

    Cheers
    Adam