How can I write to the console with CA-Datacom?

[Jim Trowbridge]

​I now have a project starting that is looking at using triggers and stored procedures (preferably SQL not COBOL), to trigger a console write when a particular table has record(s) updated or added.
There will be automation to pick up whatever that message is and do stuff elsewhere.

So my question is, has anyone done this? Any help, tips, tricks, pitfalls I should know about?

Is there an easier way?

------------------------------
DBA
Bendigo and Adelaide Bank
South Australia
------------------------------

[Owen Williams]

Hi Jim,

If you are wanting to track all maintenance commands for all rows in the target tables then I would recommend you consider Change Data Capture (CDC) for this project, as it has much lower overhead than SQL triggers and procedures, The CDCU program (that you code yourself) could then take the appropriate action directly (without the need for console messages).

I would caution against using the console for this because..

1. All console messages have to be scanned by automation to see if it matches any of the rules defined. This can be significant overhead.
2. The console is shared between all tasks on the LPAR. There is buffering and overhead in managing messages from all those tasks to ensure that the log is in sequence. Flooding the console with messages (if many rows are added/updated) can lead to delays to the whole lpar when the buffers become full.
3. If you have written a message to the console about some rows added/updated, what happens if that transaction subsequently invokes rollback/backout? The console message cannot be "backed out" and your process will be triggered but the rows/updates will no longer be there.

For all these reasons I believe CDC is a much better option, as a properly coded CDCU would only process committed logical units of work, and would eliminate the console overhead entirely.

Cheers,
Owen

------------------------------
Technical Consultant
Redcentric PLC
------------------------------

[Jim Trowbridge]

Owen,

Thanks for your reply.
We do have a different project that is going to need CDC, but this one is not quite the same. This time I understand they will generate very few updates and the update itself is not meant to be propagated.
So all they need is a way of picking up that the update occurred. Which is why a console message seems ideal.
We have plenty of automation going on, one more won't change anything.

Cheers,
Jim

------------------------------
DBA
Bendigo and Adelaide Bank
South Australia
------------------------------

[Clyde MCPHERSON]

​Jim,

Not sure what happens when you insert a row into the system table SQL_CONSOLE from a program (or if you even can do that from a stored procedure), but if you do that via DBSQLPR, it does generate a console message. For example:
INSERT INTO SQL_CONSOLE (MUF_NAME,CONSOLE_COMMAND)            
      VALUES (CURRENT DATACOM MUF_NAME,'TEST CONSOLE MESSAGE');
generates:
DB00626I - HISTORY_MUF_MESSAGE COMM TEST CONSOLE MESSAGE DBSQL 84809 STC27504-1 DBAS
In the MUF log

Might be worth playing with...

Clyde

------------------------------
Software Engineer
US Customs & Border Protection
------------------------------

[Jim Trowbridge]

​


Clyde,

I had tried that kind of INSERT into SQL_CONSOLE, but it appears to insist on only accepting input that is a M/User command.
If I try a random string like you suggest, then I get a RC 94(127):

INPUT STATEMENT:                                                   
INSERT INTO SYSADM.SQL_CONSOLE (MUF_NAME, CONSOLE_COMMAND)         
      VALUES ('MUFS01  ','TEST CONSOLE MESSAGE')                   
;                                                                  
                                                                   
___________SQL ERROR SUMMARY START___________                      
                                                                   
SQLCODE = -117, SQLSTATE=S947F, DB RETURN CODE = 94(127)           
MESSAGE = RAAT/CBS ERROR: CMD=ADDIT TBL= DBID=01000 KEY= RC=94 (127)
____________SQL ERROR SUMMARY END____________                      

INVALID COMMAND The console command is not one of those that are listed in the console command table.

Compared to this one:
INPUT STATEMENT:                                          
INSERT INTO SYSADM.SQL_CONSOLE (MUF_NAME, CONSOLE_COMMAND)
VALUES (                                                  
'MUFS01  '    ,                                           
'SQL_SOURCE_CACHE_STMTS 2K')                              
;                                                         
                                                          
___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___        

which does work:
DB00608I - SQL_CONSOLE COMPLETE - SQL_SOURCE_CACHE_STMTS 2K ADMAB91Y 74780 JOB12868-2 ADMAB91


I am curious how it is that your example works!

We are running Datacom 15.1 just in case that makes a difference.

Cheers,
Jim

------------------------------
DBA
Bendigo and Adelaide Bank
South Australia
------------------------------

[Umberto Comotti]

Jim,
the DBSQLPR query actually gets a -117 because of the DB RC 94(127) but on the MUF JESMSGLG you can find the message:
DB00626I - HISTORY_MUF_MESSAGE COMM TEST CONSOLE MESSAGE ......
provided you have HISTORY_MUF_MESSAGES set to YES.
Umberto

------------------------------
Principal Support Engineer
Broadcom
------------------------------

[Owen Williams]

The ability to write a "comment" console message in MUF was part of an idea raised by Bill Matthias way back in 2016 https://community.broadcom.com/participate/ideation-home/viewidea?IdeationKey=6b1e8876-b7a2-4ed4-9f5c-bdd2f3bab143 . Despite receiving multiple votes it has been "Under Review" since 2018. (with the Broadcom ideation system still so badly broken, the calls for additional input from the user community do not get through to members). It appears that Jim's requirement is another up-vote for that idea.

And now for a "workaround" that works well for me. It does not require HISTORY_MUF_MESSAGES YES, and does not generate the RC 94(127) and -117.

INSERT INTO SYSADM.SQL_CONSOLE (MUF_NAME,CONSOLE_COMMAND)
      VALUES (CURRENT DATACOM MUF_NAME,
              'MESSAGE NO,DB00135 TEST CONSOLE MESSAGE');

....results in...

SMUF00:DB00608I - SQL_CONSOLE COMPLETE - MESSAGE NO,DB00135 TEST CONSOLE MESSAGE jobname rrrrr JOBnnnnn-1 userid

The "MESSAGE NO,DB00135" is a low-overhead valid console command to MUF to turn off message DB00135I. (Since that's the default setting anyway for that message, it makes no difference to MUF processing). The rest of the "command" after the initial space is treated as a comment (not processed) but it is echoed in the DB00608I message that it written to the console log.

I consider this a "workaround" as MUF still has to process the redundant command, and the remaining space available for the intended "user message" is limited. But this might provide you with a solution to start your project.

------------------------------
Technical Consultant
Redcentric PLC
------------------------------

[Jim Trowbridge]

Owen,

 That SQL works for me – so a big thank you!

 And yes, I would give Bill's idea a big thumbs up!!

 Cheers,

Jim

------------------------------
DBA
Bendigo and Adelaide Bank
South Australia
------------------------------

[Jim Trowbridge]

Umberto,

I tried your example - had to add the HISTORY_MUF_MESSAGES YES startup option, and that worked as you said.
The DB15 manual has the option as HISTORY_MUF_MESSAGE which didn't work, confused me for a minute.

So thank you!

Jim

------------------------------
DBA
Bendigo and Adelaide Bank
South Australia
------------------------------

[Don Johnson]

In my testing, I found that using a STATS 8 function allowed for the longest practical text string (50 chars). The EOJ command is shorter, but you can only use it once! :)

If your SQL_CONSOLE command is longer than about 50 chars, the message text will be split into two lines, which could interfere with AutoOps routines. This is how it looked:

10.13.09 JOB51712   MY15MUF:DB00608I - - SQL_CONSOLE COMPLETE - STATS 8 HERE IS SOME TEXT  336            
   336              FOR A CONSOLE DISPLAY..... (rest of line truncated)

This was my good input to the DBSQLPR program :

//SYSIN    DD  *
INSERT INTO SYSADM.SQL_CONSOLE                                                  
VALUES('MY15MUF','STATS 8 HERE IS SOME TEXT FOR A CONSOLE DISPLAY...');
/*                                                                              

You can use any DBID for the STATS function; if you select a DBID that does not exist, you will first get a message:

DB00622I - COMM STATS 8 IGNORED, NOT OPEN

Don