Partitioning based on Change Number Range

[santiagolc]

Hi, 

I am working with Signavio Process Intelligence extracting data from SAP. The problem I am facing is that when I tried to extract the CDPOS table in advanced mode with this code (documentation):

tableSyncConfigurations:
alias: cdpos
sql: |
SELECT
CONCAT(MANDANT, OBJECTCLAS, OBJECTID, CHANGENR, TABNAME, TABKEY, FNAME, CHNGIND) AS c_key,
CURRENT_TIMESTAMP() AS c_fetchdate,
MANDANT,
OBJECTCLAS,
OBJECTID,
CHANGENR,
TABNAME,
TABKEY,
FNAME,
CHNGIND,
VALUE_NEW
FROM CDPOS
WHERE CHANGENR >= :changenr AND CHANGENR <= :max_changenr
keyColumn: c_key
mostRecentRowColumn: c_fetchdate
parameters:
- name: changenr
initial: '0000000001'
idformat: '%010d'
pagesize: 100000
type: id

It was bringing me 0 rows/records. I do not understand where I have to define the max_changenr value, because when I removed that variable and directly put the values from one changenr to another there if it worked.

I appreciate your help, greetings.

[daniel_hesseling]

Hi,
Please try these two things:
1. Be aware that you also need to define the initial parameter. This can be e.g. queried from CDHDR table based on UDATE column by taking the minimum changenr from CDHDR from the date you want to extract your data from:

parameters:
- name: changenr
initial: '0000000001'

2. If you get less data then expected, try to increase your pagesize. In rare occasions the extraction might stop because there is a gap in the changenumbers based on you filter criteria. Hence the extraction stops due to not finding additional records based your defined pagesize. 

In your case I suppose changing the initial value might help. Maybe from range 0000000001 to 0000100000 there are no changes on your database due to archiving reasons.

Best regards
Daniel

[santiagolc]

Hello Daniel,

Thanks for the reply.
1-Related to the initial parameter I have defnied one, I have quickly copied the script from the SAP documentation. The initial one defined is '0221754612'
2- I am going to try this point of increasing the pagesize.
But I have another question or maybe a more specific one: do I have to define the variable MAX_CHANGENR? or is it like a global variable that takes the sum of initial + pagesize? or do I have to define a top number? like the maximum changenr?

Thanks, best regards.

[jeromerister]

Where you able to resolve? If so how? I am facing very similar experience with CDPOS. The extraction with date is failing so I was trying to use the changenr.

[daniel_hesseling]

Hi, referring to the specific questions:

1. You can not take the CHANGENR specified in the example but need to take the correct one for you system queried from CDHDR based on your desired starting date.
2. MAX_CHANGENR does NOT need to be defined specifically. It will be created by the set pagesize / when no data is available anymore in a page.

On top of that since our last release you can look into automated pagination which makes it obsolete to use Adv. Extraction here.
Please refer to this documentation page:
https://help.sap.com/docs/signavio-process-intelligence/user-guide/pagination?locale=en-US

By using this feature and setting a SQL Filter which looks like this you are able to run an initial extraction via standard extraction with automated pagination:

OBJECTCLAS IN (<specify neccessary objectclas>)
AND CHANGENR >= ( SELECT min(changenr) FROM CDHDR WHERE udate >= '$delta' AND OBJECTCLAS IN (<specify neccessary objectclas>))
Example:

Then you would just need to add an dynamic time variable (called "delta) containing your desired start date from where the data should be extracted.

Best regards

[daniel_hesseling]

.