07-16-2008 9:54 PM
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
07-16-2008 10:01 PM
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.
07-16-2008 10:01 PM
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.
07-16-2008 10:03 PM
Thanks Srilatha. Thats what I did initially but rejected in review as its going to impact performenance.
JMC
07-16-2008 10:09 PM
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
07-16-2008 10:12 PM
Thanks Naren.
Sorry for asking a dummy question, but I was asked to look for unprocessed status. What does that mean ?
Thanks,
JMC
07-16-2008 10:16 PM
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
07-16-2008 10:19 PM
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,
07-16-2008 10:23 PM
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
07-16-2008 10:40 PM
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
07-16-2008 10:53 PM
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
07-16-2008 11:12 PM
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
07-16-2008 11:16 PM
Naren,
How does the status get change to process in BDCPS ? Do I need to change it ?
Thanks,
JMC
07-16-2008 11:23 PM
-- 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
AS
-- 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',
<PrimaryKeyFields>
From deleted
go
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!
07-17-2008 2:28 PM
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
07-17-2008 4:24 PM
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.
07-17-2008 4:33 PM
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
07-17-2008 4:36 PM
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.
07-17-2008 6:09 PM
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.
07-17-2008 6:13 PM
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
07-17-2008 6:23 PM
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
07-17-2008 7:12 PM
07-17-2008 7:15 PM
07-17-2008 7:17 PM
If for some reason your requirement changes tomorrow and want to generate matmas idocs then just schedule the program RBDMIDOC or run tcode BD21. This program will read all the unprocessed change pointers with message type MATMAS and mark them as processed.
Good luck.
Dont forget to mark the thread as answered if your problem is solved.
Regards,
Naren