Results 1 to 9 of 9

Thread: DB nodes sharing the same database session

  1. #1

    Default DB nodes sharing the same database session

    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?.
    Code:
    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. #2
    Contributor
    Join Date
    Dec 2006
    Location
    Boston
    Posts
    485

    Default

    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. #3

    Default

    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. #4
    Contributor
    Join Date
    Dec 2006
    Location
    Boston
    Posts
    485

    Default

    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. #5

    Default

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

    Cheers,
    Adam

  6. #6

    Default

    Please report this issue to Lavastorm Analytics Support at DesktopSupport@lavastorm.com or via the Customer Portal at https://na3.salesforce.com/sserv/log...0D300000000TD6.

    Thank you.
    Last edited by rabbott; 09-04-2012 at 04:28 PM.

  7. #7
    Lavastorm ANZ
    Join Date
    Jun 2007
    Location
    Melbourne, Australia
    Posts
    58

    Default

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

    Thanks,
    Mario

  8. #8

    Default Workaround Solution

    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. #9

    Default

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

    Cheers
    Adam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •