db2 z/os trigger using where clause what keywords are available for comparison?

don isenstadt

db2 z/os trigger using where clause what keywords are available for comparison?

Hello,
We are using triggers for data propagation and we need to change one to:

When (CURRENT CLILENT_APPLNAME <> 'XYZ' AND PROGRAM <> 'XYZ')

The problem is with PROGRAM .. we get a -206 so I'm assuming that we can't use that keyword.

My questions are:

1) Is there some other supported keyword that we could use instead? ie. PACKAGE? 

2) Where is the list of supported keywords that are allowed with the WHEN clause for triggers?

    I looked in the Appl Programming and SQL guide SC27-8845-02 and found nothing.

 Thanks,

Don Isenstadt

Daniel Luksetich

db2 z/os trigger using where clause what keywords are available for comparison?
(in response to don isenstadt)
This may be a challenge



Getvariable('SYSIBM.PACKAGE_NAME') is the variable, but I’ve never used it in a trigger and suspect it will be the trigger package name.



Other thoughts?



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: don isenstadt <[login to unmask email]>
Sent: Tuesday, May 21, 2019 9:25 AM
To: [login to unmask email]
Subject: [DB2-L] - db2 z/os trigger using where clause what keywords are available for comparison?



Hello,
We are using triggers for data propagation and we need to change one to:

When (CURRENT CLILENT_APPLNAME <> 'XYZ' AND PROGRAM <> 'XYZ')

The problem is with PROGRAM .. we get a -206 so I'm assuming that we can't use that keyword.

My questions are:

1) Is there some other supported keyword that we could use instead? ie. PACKAGE?

2) Where is the list of supported keywords that are allowed with the WHEN clause for triggers?

I looked in the Appl Programming and SQL guide SC27-8845-02 and found nothing.

Thanks,

Don Isenstadt



-----End Original Message-----

don isenstadt

RE: db2 z/os trigger using where clause what keywords are available for comparison?
(in response to Daniel Luksetich)

Dan,

You are correct .. the package_name is the trigger name so useless to us.. 


We are going to use a user defined global variable that will be set in the program we want to

exclude.. so if it matches the program name we exclude in the trigger ... The variable will default

to null .. 

-don 

Thanks for responding

Don Isenstadt

Michael Hannan

RE: db2 z/os trigger using where clause what keywords are available for comparison?
(in response to don isenstadt)

This seems to be essentially the same Question as: "DB2 11 for z/OS - Can a Trigger Determine Package Name for an Update (or INSERT, or DELETE)?", if I understand correctly, which I posted a last reply to on 12 April.  I don't know if nything on that thread was actually helpful, or just wild ideas.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Daniel Luksetich

db2 z/os trigger using where clause what keywords are available for comparison?
(in response to Michael Hannan)
Great answer Michael! I think it would work just fine, and the application wouldn’t have to change.

Cheers,

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Michael Hannan <[login to unmask email]>
Sent: Wednesday, May 22, 2019 3:55 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os trigger using where clause what keywords are available for comparison?



This seems to be essentially the same Question as: "DB2 11 for z/OS - Can a Trigger Determine Package Name for an Update (or INSERT, or DELETE)? https://www.idug.org/p/fo/st/thread=48896 ", if I understand correctly, which I posted a last reply to on 12 April. I don't know if nything on that thread was actually helpful, or just wild ideas.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



-----End Original Message-----