Skip to Content
avatar image
Former Member

Dynamic SQL ALTER PARTITION in Stored procedure

We created multi store tables and we want to move the partitions every quarter from memory to disk.

DLM don not work for Multi store tables, so we wrote a stored procedure to move the partitions to the disk.

For this we wrote a dynamic sql in the stored procedure for the alter statement.

My alter statement code looks like below

alter_stmnt := 'ALTER TABLE "SCHEMA"."TABLE1" ALTER PARTITION("DATE") USING EXTENDED STORAGE'||:patirion_range;

--run alter statement

EXEC alter_stmnt;

:patirion_range value is ('2017-07-01' <=VALUES< '2018-01-01')

When i run the stored procedure i got the below error

transaction rolled back by an internal error: internal error: Unable to copy data to Extended Storage: Unable to find the anchor node

I see on couple of blogs, we can write DDL in the stored procedures. But not sure why i'm seeing this error?

Am i missing anything when using the alter statement in the stored procedure?

Anyone wrote any SP to move the partitions to the disk, if so can you please share the code?

Thanks

Srini

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 15 at 12:48 AM

    Hi Srinivas,

    I don't have an answer on the error you are getting with the dynamic DDL statement, but I do have good news with regards to DLM. The latest release of DLM ( SAPHDW02_2-81002771.ZIP ) does include support for multistore tables. Unfortunately the documentation specific to multistore tables is not yet complete, but the configuration follows the same pattern that is used for configuration DLM for extension nodes. Since the documentation was not ready for the December release, the multistore table support was not highlighted at that time. The documentation is planned for inclusion in the next Data Warehouse Foundation release in Q2/2018.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Robert,

      Thanks for your response.

      We have the same version as you mentioned 1.7.2

      I was able to create life cycle profile for multi store tables, but when i run the profile, it is not moving the data to the extended store.

      Looking for help document to see if i'm doing it correctly.

      Thanks

      Srini