Re: Identity_Val_Local() on a DDM File

Thanks Mike,

In this case I'm only interested in one record at a time.
From what I've seen / read so far I feel pretty happy about using it.
Without the trigger firing it is processing at about 0.7s for 1000 inserts
which I can live with.

Anyway, thanks so much for taking the time to help out.
I need to make some time to pay back the favour - to this forum, and I
will, just ridiculously busy at the moment.

best regards,
Craig

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

Hi Craig,

I've not used FINAL TABLE extensively, but for the dozen or so times I
have, it has worked reliably and I've not seen any performance problems
with it.

Note: if your INSERT adds multiple rows, FINAL TABLE returns multiple
rows. In that case, if all you're interested in is the last identity
column value added, then you'll want to do:

select max( ID ) as MAX_ID from FINAL TABLE ( insert ... )

I've not done a lot of 3 part SQL across systems. When I have used it,
I've
not had any triggers involved, and have not had to mess with the library
list on the remote system. Hopefully someone else can sound off on how to
manipulate the library list on the remote system. I imagine people who do
need that likely use a specific user that has a default library list in a
default job description on the remote system.

Mike

On Thu, Jan 18, 2018 at 10:08 AM, Craig Richards <craig@xxxxxxxxxxxxxxxx>
wrote:

While on this subject -

Another small issue I have...
On this insert I'm firing a trigger, which works swimmingly until the
trigger program tries to open some files ( from the same library )
They library is not in the library list of the job running on the RMT
system and so of course the trigger program file opens fail.

Do you know off-hand if there is a standard way to set the library list

as

part of the connection or as an initialisation process?

I don't really want to qualify the tables in the trigger program or mess
around with library lists every time the trigger is called.
Thanks as always,
Craig

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

Hi Craig,

This alternate method of getting an identity value just added on a

remote

system just worked in a test for me:

select ID from final table ( insert into RMT_SYS.RMT_SCHEMA.TEST_ID (
TEST_DATA ) values 'B');

--result
3

...substitute the name of your identity column in place of "ID".

HTH,

Mike

On Thu, Jan 18, 2018 at 12:13 AM, Craig Richards <

craig@xxxxxxxxxxxxxxxx

wrote:

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

--
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

--
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