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.
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
Result in HANA
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
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
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
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
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
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
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
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
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
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?
thanks
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.