Hi i need to update a some objects from “VIEW OF” and when try have this error “ERROR NAT3047”
The code is very simple it’s a
R1. READ view of
IF condition
UPDATE ( R1. )
END-TRANSACTION
END-IF
END-READ
this is my totals variables
TOTAL LEIDOS…: 3.160.003
TOTAL ACEPTADOS…: 46.285
TOTAL ACTUALIZADOS…: 255
where TOTAL LEIDOS its from the view
TOTAL ACPETAODOS the objects that passed
TOTAL ACTUALIZADOS the objects that i need to update.
thanks for reading and any help it’s welcome here!
NAT3047 indicates you have too many records on hold. You are reading and holding 3m records and updating 255 of those.
There are different approaches that can be used, but for what you describe, something like this might be better:
R1. READ view1…/* view1 has just the fields needed to evaluate “condition”
IF condition
G1. GET view2 ISN(R1.)
/ move updated values to “view2”
UPDATE (G1.)
END TRANSACTION
END-IF
END-READ
This ensures that only records needing to be updated are put on the hold queue at a small cost of re-reading the records to put them on hold. You can use MULTI FETCH on the READ statement to improve performance.
Your original program puts lots of records on the hold queue and only releases them when “condition” is satisfied. If a large portion of the records being read are also being updated, you can use a periodic ET to release - something like:
R1. READ …
ADD 1 to #ET-CTR
IF condition
UPDATE (R1.)
/* optionally, do ET here and reset counter if ET for each update is needed
END-IF
IF #ET-CTR > 100
RESET #ET-CTR
END TRANSACTION
END-IF
END-READ
END TRANSACTION
Avoid using a WHERE clause on the READ statement with this approach.
1 Like
When you have an UPDATE inside a READ or FIND loop NATURAL generates a READ and HOLD call to ADABAS in case you want to actually update the record. In your case the update is conditional but the records are still being held. Thus you held too many records and exceeded the limit (hence 3047). I suggest you either add a counter and do an ET every 50 records to release them or:
Inside the IF do a GET to reread the record you are updating and then have the UPDATE refer back to the GET so only the actual records to be updated are held (still should have a counter). Final option if it is a FIND to move the condition into the WITH clause so ADABAS only returns records to be updated.
Eugene (Gene) Miklovich
ADABAS/NATURAL Systems Support and DBA
Cell: 916-202-7047
Normal Hours: 8am-4pm E.T.
Please call or text if assistance needed outside these hours.
2 Likes
Hola Nacho,
La explicacion de Douglas es correcta.
El problema es que estas HOLDEANDO muchos registros en comparacion con la cantidad que actualizas. ADABAS bloquea todos los registros que leeas (aunque no entren en el IF) si tenes un UPDATE, y tiene un limite de registros holdeados sin hacer END TRANSACTION
Lo mas performante es hacer un get en una segunda View, la cual contenga solo los campos que queres actualizar. Y llevar un control de transacciones, por ejemplo cada 50 registros actualizados hacer ET.
Saludos
Mariano
1 Like
Thank you so much!, your answer is the solution, sorry for my ignorance i’m trainee in this haha
greetings from argentina!