Re: SQL column size in CLLE

Um, I never suggested you were “newfangled”.

Only that the specific customer tends to live in the 2000s (although newer hardware) and they would need a very simple solution. They won’t allow the system managed journals (don’t know why but I suspect the manager had a bad experience once long ago).

No application layer HA.

I had forgotten about that command, thank you.


Jim Oberholtzer
Agile Technology Architects

On May 28, 2025, at 4:28 PM, Rob Berendt <robertowenberendt@xxxxxxxxx> wrote:

Really? I'll have my 39th year at this company come August, and I'm too
new fangled? Oh, well.

Be mindful if they are using any software based H/A products like Mimix.
They prefer to manage their own receivers.

Have you looked at this one?
https://www.ibm.com/docs/en/i/7.6.0?topic=services-delete-old-journal-receivers-procedure

On Wed, May 28, 2025 at 4:30 PM Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>
wrote:

Rob,

While I agree with your idea of a stored procedure, I have to be mindful
of the customer's developers. In the end they need to manage this.

The CLLE (that in and of itself is pushing them unfortunately) is quite
simple; read the file and delete the older receivers. Same reason I did
not use VARCHAR in CLLE

I put the SQL into a source file member, then used RUNSQLSMT. It’ll
take some convincing to allow SQL as opposed to using commands to list it
out to outfiles and use those. That’s way too much like work.




Jim Oberholtzer
Agile Technology Architects

On May 28, 2025, at 2:12 PM, Rob Berendt <robertowenberendt@xxxxxxxxx>

wrote:

Multiple ways to skin this cat.
One, learn how to use variable length character columns in CLLE. Easier
said than done.
Two, 'cast' the variable length columns to character.
SELECT *char*(JOURNAL_RECEIVER_LIBRARY, *10*) as RCVLIB
,*char*(JOURNAL_RECEIVER_NAME, *10*) as RCVNAM
Three, stop using CL and use a stored procedure to process stuff like

this

(create, followed by execution)
CREATE PROCEDURE Change_IFS_Owner
(IN CURRENT_OWNER *VARCHAR*(*10*),
IN NEW_OWNER *VARCHAR*(*10*),
IN START_PATH *VARCHAR*(*50*) )
LANGUAGE SQL MODIFIES SQL DATA
SET OPTION DATFMT = *ISO
P1: BEGIN
DECLARE WORK_PATH_NAME *VARCHAR*(*500*);
DECLARE COMMAND *CHAR*(*500*);
*-- Ensure this is large enough.* DECLARE END_TABLE *INT*

DEFAULT

*0*;
DECLARE C1 CURSOR FOR
select path_name

*-- 'CHGOWN OBJ(''' concat path_name concat ''') NEWOWN(' concat
new_owner concat ')'*

*--, QCMDEXC('CHGOWN OBJ(''' concat path_name concat ''') NEWOWN('
concat new_owner concat ')')*
FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS(START_PATH_NAME *=>*
START_PATH,
SUBTREE_DIRECTORIES

*=>*

'YES'))
WHERE object_owner=CURRENT_OWNER
and path_name not like '%' *concat* x'7D' *concat* '%';

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET END_TABLE = *1*;

*-- DECLARE EXIT HANDLER FqsysOR SQLEXCEPTION*
*-- SET DEPT_SALARY = NULL;* OPEN C1;
FETCH C1 INTO WORK_PATH_NAME;
WHILE END_TABLE = *0* DO
SET COMMAND = 'CHGOWN OBJ(''' *concat* work_path_name

*concat*

''') NEWOWN(' *concat* new_owner *concat* ')';
CALL QSYS2.QCMDEXC(COMMAND);
FETCH C1 INTO WORK_PATH_NAME;
END WHILE;
CLOSE C1;
END P1
;
CALL ROB.Change_IFS_Owner('JREINKIN', 'TRACIL', '/Banking');

On Wed, May 28, 2025 at 2:19 PM Jim Oberholtzer <

midrangel@xxxxxxxxxxxxxxxxx>

wrote:

I am running this SQL in a CLLE program:

Runsql ('CREATE TABLE QTEMP/LISTJRN AS (
SELECT JOURNAL_RECEIVER_LIBRARY as RCVLIB
,JOURNAL_RECEIVER_NAME as RCVNAM
,DETACH_TIMESTAMP as TIMESTMP
FROM QSYS2.JOURNAL_RECEIVER_INFO
WHERE DETACH_TIMESTAMP IS NOT NULL
AND SUBSTR(JOURNAL_RECEIVER_LIBRARY, 1,1) <> ''Q''
AND DETACH_TIMESTAMP < CURRENT_TIMESTAMP - 14 DAYS
ORDER BY DETACH_TIMESTAMP)
WITH DATA')
COMMIT(*NONE)

In SQL runscrips, the SQL works great, until I go to use the columns
RCVLIB and RCVNAM. The SQL produces a file with varchar output which

the

CLLE does not like.

How to I put a size on the two columns (ideally Char 10) so the CLLE
compiler won’t complain?
--
Jim Oberholtzer
Agile Technology Architects




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.