Re: embedded sql and alwcpydta

AFaIK the precompiler will maintain an update capable cursor as "updatable" [see that fake word and "deletable" for delete capable references in the DECLARE CURSOR documentation], knowing that it is referred to by an UPDATE WHERE CURRENT OF statement. Even if there is a concern for any particular cursor, just specify the FOR UPDATE [OF] clause. Unlike preventing temporary data generally, using that FOR UPDATE OF clause avoids the possibility that other non-update [AKA read-only] cursors will be prevented from optimal I\O characteristics due to the Allow Copy Data feature being denied. Similarly the FOR READ ONLY clause prevents an otherwise update capable cursor from remaining "updatable" even if not referenced in a WHERE CURRENT OF; the DECLARE CURSOR documentation even notes that if "a cursor is not going to be used in a Positioned UPDATE or DELETE statement," then "it should be declared as FOR READ ONLY." So probably just leave the ALWCPYDTA(*YES | *OPTIMIZE), and explicitly tell the SQL pre-compiler of any plans to update via any particular cursor; and that FOR UPDATE OF is actually required when the cursor has an ORDER BY, for which the query implementation by temporary data copy is most probable.

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzh2dclcu.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/sqlp/rbafycurs5a.htm

Regards, Chuck

JDHorn@xxxxxxxxxxxxxx wrote:

when embedding sql in rpg, when selecting via a cursor and doing
an update/delete using "current of cursor", will the compiler
"know" you don't want to create a temporary copy of the data or
might it do it anyway?

if the above is that it might copy the data, is there a compiler
directive for alwcpydta(*no)?