NLS Archival of Data

[Former Member]

My query is on NLS Implementation of Sybase IQ.

Suppose, we have archived our data based on time-slice (We have two options : time-slice / request-based).

During the first archival, the data was archived for years 2010,2011 and 2012. Are partitions created based on time in IQ ?

Next time when I archive the data for 2010, will a new partition be created in NLS storage or will the data be send to the previous archived partition?

If a new archived partition is created in NLS storage, which partition will the query search if criteria is 2010 ? New or the older partition ?

I need to know how the data is stored in NLS archived storage.

Can the archival process from BW on  HANA to SYbase IQ be automated on a timely basis instead of manually doing it ?

It would be really helpful, if anyone could provide a link which explains the whole process completely.

Thanks in advance.

[Former Member · Accepted solution]

Hi Pooja,

This link should be useful:

Sybase IQ as a Near-Line Storage Solution - Data Warehouse Management - SAP Library

Here you will also see sections on Administration of Partitions in IQ and Scheduling Data Archiving Processes.

__________________________________

During the first archival, the data was archived for years 2010,2011 and 2012. Are partitions created based on time in IQ ?

- Partitions are created based on the archive request.

Next time when I archive the data for 2010, will a new partition be created in NLS storage or will the data be send to the previous archived partition?

- A new partition is created either by splitting the max partition or splitting the partition from the previous archive request.

If a new archived partition is created in NLS storage, which partition will the query search if criteria is 2010 ? New or the older partition ?

- The search queries the table, and IQ knows which partition holds the data.

I need to know how the data is stored in NLS archived storage.

- You can view the partitions in SAP Control Center -> Administration Console; expand IQ Servers -> Schema Objects -> Tables; choose Tables; select your table in the right panel; choose Properties from the dropdown; select Partitions.

- You can also run the report RSDA_SYB_PARTITION_MONITOR from BW to monitor the partitions, and to make changes to how the partitioning is managed.

Can the archival process from BW on  HANA to SYbase IQ be automated on a timely basis instead of manually doing it ?

- Yes - see the link mentioned above.

Hope this is helpful.

- Eric

[Former Member · Accepted solution]

Thanks Eric. The link was really helpful

.

I have one more question. I may be wrong in understanding the concept. Please clarify my doubt.

As you said, the query will search the table. But the table is archived, so it is deleted from the BW system.

Does that mean, the query will search for all the partitions of the table in IQ?  Suppose for a table

Archive Request 1 partition : data for years 2010, 2011 , 2012

Archive Request 2 partition: data for year 2010

Will the query search both the requests ? Doesn't it affect the performance ?

And , while creating an archival process we specify time characteristics as the primary partition.

So, in the Archive request 1 partition, will there be separate sub-partition for years 2010, 2011 , 2012 respectively ? So that, as and when the query search is for 2010, it goes and searches only the 2010 partition ?

Or does IQ does any indexing ?

Thanks in advance

[former_member421817 · Accepted solution]

Pooja,

  To add to Eric's helpful answers, I may add that once you archive / nearline a request, the online system locks that particular range. When you execute a query on that range of data, the system automatically directs it to the Nearline / Archive system to retrieve the data from it. There is not a huge impact on performance.Hope this helps.

[Former Member · Accepted solution]

Hi Pooja,

My answers below:

Does that mean, the query will search for all the partitions of the table in IQ?  Suppose for a table

Archive Request 1 partition : data for years 2010, 2011 , 2012

Archive Request 2 partition: data for year 2010

- The partitions are based on the archive request, but your example is not correct, because the year 2010 is already archived and locked in the first request.

     - If you make requests based on quarter, you will have the data partitioned by quarter. If by year, the partition will be by year. And if by a range of years, that will be a single partition. So your first archive request would create a single partition with three years of data.

Will the query search both the requests ? Doesn't it affect the performance ?

- If the query is based on a date range, it will only search the partitions holding the date range. It will do this by the request ID. If your query does not specify a date and uses another criteria, it will search all partitions.

And , while creating an archival process we specify time characteristics as the primary partition.

So, in the Archive request 1 partition, will there be separate sub-partition for years 2010, 2011 , 2012 respectively ? So that, as and when the query search is for 2010, it goes and searches only the 2010 partition ?

- This is done by the request. You should plan your requests or your archiving process chains in BW to meet your requirements.

Or does IQ does any indexing ?

- IQ does create indices on all of the columns. In addition, the archiving process will create some indices. BW will create a view for the archive and will create indices on the columns used in the view's where clause. In addition to the IQ indexing, BW is also aware of which archive request holds which time slice, so the performance is optimized on both ends.

Hope that helps,

Eric

[Former Member · Accepted solution]

Thanks Eric and Makarand

It was really helpful..

[former_member608991]

Good discussion. Out of interest, does any partition gets created in SAP BW side during data transfer to Sybase IQ NLS ?