topic Re: Change Pointers in Application Development and Automation Discussions

Change Pointers

Former Member — Wed, 16 Jul 2008 20:54:43 GMT

Hi all,

Have a requirement to read all newly created/changed data from tables MARA and MARC. I was asked to get this data extracted thru change pointers so that that will not effect performenance. Can someone please help me how can I achive this thru change pointers ? Not thru reading from tables CDHDR or CDPOS as they grow big.

Thanks in advance.

JMC

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:01:36 GMT

Hi,

If you just need to fetch newly created/changed material data then fetch data from MARA based on ERSDA (Creation date) and LAEDA (Date of Last Change) by giving proper date range. Then fetch data from other relevant tables.

Regards,

Srilatha.

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:03:36 GMT

Thanks Srilatha. Thats what I did initially but rejected in review as its going to impact performenance.

JMC

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:09:49 GMT

JMC,

If you want to use change pointers then you HAVE to read CDHDR and CDPOS tables as they are the change pointer tables.

There are function modules available to read those tables based on start date, start time, end date and end time which probably retrieves less number of records.

Thanks,

Naren

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:12:46 GMT

Thanks Naren.

Sorry for asking a dummy question, but I was asked to look for unprocessed status. What does that mean ?

Thanks,

JMC

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:16:22 GMT

Every time you process change pointers, their status will be set as processed. may be there should be a field in CDPOS table which tells that. let me check.

Meanwhile here are the function modules

CHANGEDOCUMENT_READ_HEADERS

CHANGEDOCUMENT_READ_POSITIONS

Edited by: Naren K Someneni on Jul 16, 2008 4:17 PM

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:19:53 GMT

JMC,

I am sorry. I was bit confused. Those tables I gave you are for change documents not change pointers.

The change pointer tables are BDCP, BDCPV etc...let me check...

Sorry for the confusion. For change pointers you dont use CDHDR and CDPOS.

Thanks,

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:23:34 GMT

JMC,

The tables you use are BDCP and BDCPS. The table BDCPS has a field called Process indicator which tells you whether they are already processed or not. if your requriement is to collect and transmit unprocessed changes then this field should be blank.

Hope this helps. let me know if you need more info.

Thanks,

Naren

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:40:53 GMT

Thanks Naren.

Can you please let me know programaically what should be my approach ?

Do I need to read BDCP first and then read BDCPS based on CPIDENT for unprocessed ?

How do I get rest of the information from MARA and MARC tables ?

What kind of configuration do I need ? I was asked to put * in all fields which ever I need to track from table MARA and MARC.

Thanks,

JMC

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 21:53:09 GMT

JMC,

Do you have to generate a MATMAS idoc for those material changes or you just want to read MARA and MARC changes.

If it is only to pull MARA and MARC changes then from the BDCP table you have to search for change pointer with field TABNAME containing MARA or MARC only. if you want to know which field has been changed in these table tables then you can get that field name too from the field FLDNAME.

Yes. You have to read all unprocessed entries based on CPIDENT field. (PROCESS field should be blank).

Use the FM CHANGE_POINTERS_READ to read change pointers.

Edited by: Naren K Someneni on Jul 16, 2008 5:00 PM

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 22:12:37 GMT

Yes Naren. My requirement is to read data from MARA and MARC.

So i guess, i need to read BDCP for table MARA, get the CPIDENT and read BDCPS. From BDCPS i get the MARA key information and feild changed. Once I have MARA key, do I still need to do a select from MARA to get rest of the feild information or how does this work ?

Sorry for asking several questions.

JMC

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 22:16:24 GMT

Naren,

How does the status get change to process in BDCPS ? Do I need to change it ?

Thanks,

JMC

Re: Change Pointers

Former Member — Wed, 16 Jul 2008 22:23:22 GMT

-- All notes below in SQL, if you have another DB, you have to convert the "talk" to your specific DB --

Why not use a DB trigger + trigger table. Here are the details:

1. Have ABAP create a trigger table, one for each MARA and MARC. The table names (here's what we use inhouse), are ZD+Table name. For in this example, we'll create tables ZDMARA and ZDMARC.

These tables should contain only the following fields:

- All fields in the primary unique key.

- Additional fields: Change_DateTime, ChangeKey, ChangeGUI

*(DO NOT put any application level fields in this, you will put unncessary load on the DB server.)*

2. Wrap the trigger tables created above w/ a transport for administration.

3. Create triggers on the MARA and MARC tables, triggers are divided into "C" (change), "D" (delete), "I" (Insert). An example trigger for Delete on MARA would be:

-- deletion trigger

--creation command for MARA_DEL_DATE

CREATE Trigger MARA_DEL_DATE

on MARA

FOR Delete

-- Return if Row count is 0

If @@rowcount = 0

return

-- Calculate today's date/time

INSERT ZDMARA (Change_DateTime, ChangeKey, ChangeGUI, <PrimaryKeyFields>)

SELECT CLIENT,

-- calculate current date and time stamp

CONVERT(NVARCHAR(8), Getdate(), 112) + CONVERT(NVARCHAR(12), GETDATE(), 114),

-- create a unique GUID value

CONVERT(nvarchar(40),NEWID()),

'D',

From deleted

4. What's going to happen is that, whenever there's a change in table MARA and MARC, the change is recorded into ZDMARA and ZDMARC. You can read from ZDMARA and ZDMARC first, to get the primary key fields required to query MARA and MARC. Bing bang, quick access.

Overhead? Nah, very little. We have this in our 6TB database, no problems at all.

Make sure you remember to archive the ZDMARA and ZDMARC tables!

Re: Change Pointers

Former Member — Thu, 17 Jul 2008 13:28:22 GMT

Thank you Kevin. Excellent alternative to achieve this but here client is very pirticular about using change pointers to read MARA changes.

I am looking for sample code or approach to read from BDCP BDCPS tables. Appreciate if some one please guide me on this. Thank you.

JMC

Re: Change Pointers

Former Member — Thu, 17 Jul 2008 15:24:03 GMT

JMC,

Sorry for the late response.

Read change pointers using the FM CHANGE_POINTERS_READ. The selection is made using two time frames, event generation and event activation. You can also specify whether only processed or only unprocessed change pointers should be read. The default is unprocessed change pointers.

Once after reading those change pointers you can update the status of those change pointers in your program.

I am pasting here the sample code to give you an idea .....but better use the standard FM's...

SELECT   cpident
           FROM bdcp
           INTO CORRESPONDING FIELDS OF TABLE lt_bdcp
           WHERE cdobjid LIKE lv_car
           AND   cretime =< lv_date.
  IF NOT lt_bdcp[] IS INITIAL.
 
    SELECT cpident
           mestype
           INTO   CORRESPONDING FIELDS OF TABLE lt_bdcps
           FROM   bdcps
           FOR ALL ENTRIES IN lt_bdcp
           WHERE  cpident = lt_bdcp-cpident
           AND    mestype = i_mestyp
           AND    process NE 'X'.
 
    LOOP AT lt_bdcps INTO wa_bdcps.
 
      UPDATE bdcps SET    process = 'X'
                   WHERE  cpident = wa_bdcps-cpident
                   AND    mestype = i_mestyp.
 
    ENDLOOP.
 
  ENDIF.

Re: Change Pointers

Former Member — Thu, 17 Jul 2008 15:33:23 GMT

Thank you so much for your response Naren.

I think I am getting close.

So, from here do you suggest me to get the key and read mara directly again thru a select statement ?

Or is there an alternative way to get other fields from MARA after getting CPIDENT?

Do we need to define a message type in this case ?

Thanks,

JMC

Re: Change Pointers

former_member187255 — Thu, 17 Jul 2008 15:36:43 GMT

JMC,

If you want to create Idocs then u can use this FM MASTERIDOC_CREATE_SMD_MATMAS to create idocs for the changed materials.

You can go thru the FM to get the process of changed materials..

Hope this helps..

Chandra.

Re: Change Pointers

Former Member — Thu, 17 Jul 2008 17:09:17 GMT

You will only get the material and the field that has changed from the change pointers table. If you need other fields for that material then you have to do a select on mara and marc in your program.

Re: Change Pointers

Former Member — Thu, 17 Jul 2008 17:13:33 GMT

JMC,

Message type is required for IDOCS but not in your case. it is optional....If you had used idocs then you have to configure partner profiles with message type MATMAS. So just leave that and dont use that in your select statement....just check for table names MARA and MARC in the change pointers table.

Let me know if you need more help.

FYI...the material number which is changed/created will be found in the object value fields of BDCP. CPIDENT is just a unique change pointer ID for joining BDCP and BDCPS. It is NOT the material number.

Edited by: Naren K Someneni on Jul 17, 2008 12:14 PM

Re: Change Pointers

Former Member — Thu, 17 Jul 2008 17:23:38 GMT

Thank you so much for all the help Naren. That helps.

I will go ahead with the simple select after reading from tables BDCP and BDCPS. Will get rest of the information from MARA and MARC based on the key what i get from BDCP.

One last question, do i need to set up any configuration for change pointers ? Is it by default I am going to get change pointers created for ANY changes to mara and marc regardless of fields ? Please let me know if i need to do any set up in the system to get change pointers. I need it to be created for any updates/creates to materail master.

Thank you once again and sorry for asking several questions.

JMC