Skip to Content
Author's profile photo Tobias Koebler

How to specify a partioning command

Hi,

you have the need to partition you table that shoud replicated to HANA. No problem with SLT.

There is an additional field (PARTITION_CMD) in table IUUC_REPL_TABSTG (TX: IUUC_REPL_CONTENT) where a partitioning command can be defined for certain tables. The partition command will be added to the create statement of the table on the HANA System.

/wp-content/uploads/2013/06/0_233625.png

The partition command has to be entered in the same way as it would be defined in the SQL editor of the HANA studio, for example:

     PARTITION BY HASH (a, b) PARTITIONS 4

SLT will add the partition command when generating the SQL command to create the table. For example:

     CREATE COLUMN TABLE mytab  

          (a INT, b INT, c INT, PRIMARY KEY (a,b)) 

          PARTITION BY HASH (a, b) PARTITIONS 4

Example:

Setting within IUUC_REPL_TABSTG

/wp-content/uploads/2013/06/1_233626.png

Result in HANA

/wp-content/uploads/2013/06/2_233663.png

Note that instead of the partitioning command, each SQL parameter (e.g. for localization) is possible – but ensure that the syntax is correct – SLT is not checking this.

Best,

Tobias

Assigned Tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Harald Röser
      Harald Röser

      Hi Tobias,

      this hint for creating partioned tables was really helpfull.

      Now we would like to use for partioned tables only one node. So we added the LOCATION command, but it does not work.

      We tried following commands:

      PARTITION BY HASH (MANDT, DOCNUM) PARTITIONS 3 AT 'servername:32003' 

      and also

      PARTITION BY HASH (MANDT, DOCNUM) PARTITIONS 3 AT LOCATION 'servername:32003' 

      Do have any idea what is wrong?

      Kind Regards,

      Harald

      Author's profile photo Former Member
      Former Member

      Hi Tobais,

      Thanks for this blog. Its really helpful. I am able to create partition tables (2 partitions) by using this blog.

      However ,while try to create 3 partitioned table , i am getting syntax error.

      Below is partition command , I am using -

      PARTITION BY RANGE ("GJAHR") (PARTITION VALUE = '2015',PARTITION VALUE = '2014', PARTITION OTHERS)

      Syntax error , (I found in Application logs in LTRC) is

      sql syntax error: incorrect syntax near "PARTITIONVALUE": line 1 col 1454 (at pos 1454)

      When I went to setting in LTRS , I saw that 2nd partition value gets combined like this -

      PARTITION BY RANGE ("GJAHR") (PARTITION VALUE = '2015',PARTITIONVALUE = '2014', PARTITION OTHERS)

      I think this is causing the syntax error.

      DMIS version is 2011 SP07

      Could you please suggest how to solve this.

      Regards

      Kartik

      Author's profile photo Tobias Koebler
      Tobias Koebler
      Blog Post Author

      Hi Kartik,

      good question - I am not a SQL expert and do not know what HANA allows and not. SLT is just adding the command you define here to the end of the create statement that we execute on HANA. So I would think it is a syntax error.

      Can you post your question in the HANA community, play around with the commend directly on HANA or have a look into the HANA docu.

      Best,
      Tobias

      Author's profile photo Former Member
      Former Member

      Hi Tobias,

      I was wondering if it is possible to Partition the table if it is already created in HANA Database? I have just realized that the table is too big and system created alert and suggested user action: partitioning the table.

      If it is possible, can you please let me know how to do it after table is in HANA DB?

      Thanks,

      Christina

      Author's profile photo Tobias Koebler
      Tobias Koebler
      Blog Post Author

      Hi,

      maybe there is an option on HANA DB level. You should ask in the HANA community.

      With SLT you can only specify it before you start wit the replication (before the table on HANA will be created).

      Best,
      Tobias

      Author's profile photo Saritha Koroth
      Saritha Koroth

      Hi Karthik,

      For your first query on how to specify partition if it has more values, even I noticed that partition value gets concatenated to one word. So when you enter those words, please put in more spaces.

      eg-

      PARTITION BY RANGE (MTART)(PARTITION  VALUE  =  'MT01',PARTITION   VALUE   = 'MT02', PARTITION   VALUE  = 'MT03',PARTITION   VALUE  =  'MT04',  PARTITION   OTHERS)

      and so on.

      For your second query in HANA db LEVEL you can always specify the alter table statement to specify partitions for a table which is already replicated. it does allow to add partitions later on.

      sample example -

      alter table "xxx"."ZHMARA"

        PARTITION BY RANGE (MTART) (PARTITION VALUE = 'MT01', PARTITION VALUE = 'MT02', PARTITION VALUE = 'MT03', PARTITION OTHERS)

      hope it helps.

      Regards,

      Saritha K

      Author's profile photo Former Member
      Former Member

      Hi Saritha and Tobias,

      Thanks for your comments.  I put in more spaces but it didn't worked either. However, when I pressed 'enter' after every partition value ( for e.g. PARTITION  VALUE  =  'MT01')  , it worked . Partition value didn't concatenated to one word.

      It seems problem is not in HANA , it's only in SLT where we are specifying the partitioning command as command itself gets wrong (partition value gets concatenated) while saving it in LTRS.

      Regards

      Kartik

      Author's profile photo Former Member
      Former Member

      Hi All,

      Thank you Tobias for this blog and because of all your comments here I am able to do range partition.

      However, I got a question ... if I want to do range partition based on date (for example table SFLIGHT, field FLDATE) ... how do I do that?

      I tried using:

      PARTITION BY RANGE (FLDATE) (PARTITION VALUE <= '20000101'),

      PARTITION VALUE > '20000101' AND PARTITION VALUE <= '20100101',

      PARTITION VALUE > '20100101')

      I got sql syntax error: incorrect syntax near '<='

      How should I do this?

      Really appreciate any help you guys can give me. Thanks beforehand.

      Christina

      Author's profile photo Ami Tabak
      Ami Tabak

      How does one state a partitioning in the HANA studio project ?

      The examples in the HANA project studio help show how to define PK & indexes but partititoning is not covered

      Example:

      table.schemaName = "OPTIER_TRUNK";

      table.tableType = COLUMNSTORE;

      table.columns = [

      {name = "REQUEST_INSTANCE_ID";     sqlType = BIGINT;   nullable=false;},

      {name = "REQUEST_UUID";      sqlType = VARCHAR;  length=100; nullable=false; },

      {name = "COMPOUND_REQUEST_INSTANCE_ID";  sqlType = BIGINT; },

      {name = "ELAPSED_TIME";      sqlType = BIGINT; },

      {name = "UOW_COUNT";       sqlType = BIGINT; },

      {name = "START_TIME";       sqlType = TIMESTAMP; }

      {name = "URL";         sqlType = VARCHAR;  length=4000; }

      ];

      table.primaryKey.pkcolumns = ["REQUEST_INSTANCE_ID", "START_TIME"];

      and I want to partition by hash(START_TIME)

      In "normal" SQL thats easy, but in the studio ... anyone?

      Author's profile photo Former Member
      Former Member

      thanks

       

      Author's profile photo Former Member
      Former Member

      I am getting  error  dumps in SLT while converting text of table STXL to readable format. We are using include BOR to replicate table.

       

      ategory Error at ABAP Runtime
      untime Errors IMPORT_CONTAINER_MISSING
      ate and Time 24.03.2017 18:43:41

      Short Text
      Format error in IMPORT: No further container found.