Re: Identity_Val_Local() on a DDM File -- RPG400-L

Hi Mike,

Thanks for your reply - I appreciate you taking the time to try to help.
However, unless I misunderstand something, your SQL doesn't really work in
my situation.

In your example, when you insert the record with value A, the SRC system
doing the insert has no way of knowing the ID.

If I get time I might try this using a JDBC connection, I'm hopeful that
that might work.

Thanks again and best regards,
Craig

On 18 January 2018 at 01:20, Mike Jones <mike.jones.sysdev@xxxxxxxxx> wrote:

Hi Craig,

The likely difference between your test and my use, is mine references the
IDENTITY_VAL_LOCAL() function inside an SQL statement that is ALSO a 3 part
SQL referencing the remote system. See example below.

Note: I've used this in ACS Run SQL Scripts, but I think it is very likely
to work elsewhere.

Example:

create table RMT_SYS.RMT_SCHEMA.TEST_ID
( ID bigint generated always as identity
,TEST_DATA char(1)
) ;

insert into RMT_SYS.RMT_SCHEMA.TEST_ID
( TEST_DATA ) values 'A';

insert into RMT_SYS.RMT_SCHEMA.TEST_ID
( TEST_DATA ) values trim( char( IDENTITY_VAL_LOCAL() ) );

select * from RMT_SYS.RMT_SCHEMA.TEST_ID;

--Result
ID TEST_DATA
1 A
2 1

IDENTITY_VAL_LOCAL() used in a non-3 part SQL will return the last ID
assigned on the LOCAL system.

IDENTITY_VAL_LOCAL() used in a 3 part SQL will return the last ID assigned
on the REMOTE system.

HTH,

Mike

On Wed, Jan 17, 2018 at 4:40 AM, Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

Well,

It was easy enough to set this up and insert a record to a remote

machine.

But when I call identity_val_local() after the insert it just returns

null

To be clear, what I am doing is:
1. From Machine A, using 3-part SQL to insert a record into a table on
Machine B which has an always generated identity column.
2. From Machine A, trying to call identity_val_local() to find the latest
identity value assigned.

Identity_Val_Local() always returns null.
I tried this via the STRSQL command and also in an SQLRPGLE program.

I understand that this value would need to be retrieved from the job
running on the remote machine which actually does the work. I'd hoped it
might be smart enough to figure it out...
I'm lead to believe that it should work via a JDBC connection.

Mike - I thought you said this worked for you, but the scenario you
described isn't the same as far as I can tell.

thanks and regards,
Craig

On 5 January 2018 at 19:05, Craig Richards <craig@xxxxxxxxxxxxxxxx>

wrote:

That's excellent.

Thanks all for the info.
Very much appreciated.

On 5 January 2018 at 18:25, <JRusling@xxxxxxxxxxx> wrote:

Couple more, for posteriority -

https://www.ibm.com/developerworks/community/wikis/home?
lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/
Add%20QDDMDRDASERVER%20server%20authentication%20entry%

20special%20value

Add QDDMDRDASERVER server authentication entry special value

https://www.ibm.com/developerworks/community/wikis/home?
lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/
Add%20QDDMDRDASERVER%20server%20authentication%20entry%

20special%20value

Add QDDMDRDASERVER server authentication entry special value
<br />
The information in this email is confidential and may be legally
privileged.
It is intended solely for the addressee. Access to this email by

anyone

else is
unauthorized. If you are not the intended recipient, any disclosure,
copying,
distribution or any action taken or omitted to be taken in reliance on
it, is
prohibited and may be unlawful.
--
This is the RPG programming on the IBM i (AS/400 and iSeries)

(RPG400-L)

mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD