RE: SQL query to know lines of code for all the programs and it's associated modules -- MIDRANGE-L

Here are some of the queries I've used in the past to get a high level view of this information. Number_Rows will give you lines of code.

/*RPG Programs*/
SELECT system_table_schema, system_table_name, SOURCE_TYPE, system_table_member , PARTITION_TEXT, NUMBER_ROWS
FROM QSYS2.SYSPARTITIONSTAT
WHERE SYSTEM_TABLE_SCHEMA IN ('Your RPG Source Library Name here') AND SOURCE_TYPE NOT IN ('CLLE','CLP','LF','PF', 'SQLC')
order by source_type, system_table_schema, system_table_name, system_table_member;

/*CL Programs*/
SELECT system_table_schema, system_table_name, SOURCE_TYPE, system_table_member , PARTITION_TEXT, NUMBER_ROWS
FROM QSYS2.SYSPARTITIONSTAT
WHERE SYSTEM_TABLE_SCHEMA IN ('Your RPG Source Library Name here') AND SOURCE_TYPE IN ('CLLE','CLP')
order by source_type, system_table_schema, system_table_name, system_table_member;

/*DDS Source*/
SELECT system_table_schema, system_table_name, SOURCE_TYPE, system_table_member , PARTITION_TEXT, NUMBER_ROWS
FROM QSYS2.SYSPARTITIONSTAT
WHERE SYSTEM_TABLE_SCHEMA IN ('Your RPG Source Library Name here') and SOURCE_TYPE IN ('LF','PF','SQLC')
order by source_type, system_table_schema, system_table_name, system_table_member;
;

/*Menus users are assigned to*/
SELECT DISTINCT initial_menu_name FROM qsys2.USER_INFO ui ORDER BY INITIAL_MENU_NAME

/*Table information*/
SELECT * FROM qsys2.SYSTABLES WHERE table_type='T' --DML defined SQL TABLES
SELECT * FROM qsys2.SYSTABLES WHERE table_type='P' --DDS defined TABLES

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of tim ken
Sent: Thursday, June 29, 2023 7:31 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL query to know lines of code for all the programs and it's associated modules

CAUTION: This email originated from outside of the ARRT. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Hi,

Thanks for all these inputs here.

Since I want to get information related to other important things like triggers etc.

So I tried to execute the below command but it ended up with this error "OUTFILE not allowed with specified TYPE." So is there any way to get that information here in DSPFD and using some other system table like 'qsys2.syspartitionstat' etc.?

DSPFD FILE(*ALL) OUTPUT(*OUTILE) OUTFILE(MYFILE/MYLIB)

also with some limitation (by removing *all for some of the fields in above command and with *MBRLIST in 'Type of information') when ran DSPFD command and compared it's outfile field 'Number of Records' with select system_table_schema, system_table_name, system_table_member, number_rows from qsys2.syspartitionstat; for a specific source file.'Number Rows' both count matched with WRKMBRPDM (Option 8- Display Description --> Number of Records) but when i went inside that particular member and checked lines count were actually different.

also in below parameters what does 'Transaction Rate' mean exactly ?

Did you mean Row and column access control rules for RCAC here? can those be obtained using DSPFD as well if not any SQL query for the same?

Can we get PASE/Open source related information using some SQL query as well?

Can we get Commitment control information using any SQL query here?

Thanks a lot ...

On Thu, 29 Jun 2023 at 12:10, x y <xy6581@xxxxxxxxx> wrote:

No.

As others have pointed out, this task doesn't make much sense. What
problem is the requester trying to solve? It seems there's a problem
of some sort and the requester thinks evaluating program complexity is
the answer. The outlier answer is that you're being pranked. But
we'll let you rule that one out.

I'm going to go contrarian and suggest there may be some value in this
exercise. For example, if a simple CRUD (file maintenance program)
ranks high on the complexity scale, it may be time for a rewrite.
HIghly-complex programs may be loaded with technical debt, or written
by poorly-trained and inexperienced programmers, or suffering from
years of business logic changes stacked on top of other business logic
changes, all of which lead to program instability/failure.

The problem with the request is that "complexity" is highly subjective.
*Highly.*

There are two aspects of complexity: one is about the code and the
other is about the application/business rules.

A few lines of code from one of my service programs:
theta = lon1 - lon2;
dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1))
*
cos(deg2rad(lat2)) * cos(deg2rad(theta));

Is this a complex program? Code=no, application=yes.

I have a 9,000 line batch program--it creates AR, payables,
statistics, and general ledger entries from orders. If you understand
the rules of revenue accounting, the program is not complex--I used
lots of variables, subroutines, and subprocedures with meaningful
(i.e., long and descriptive) names.

You can advise the user that "lines of code" isn't a valid metric
merely because an RPG expression can be spread over several lines and
it would take moderate effort to reduce every expression to a single
line of code, which would give you a realistic number of lines of
code, again noting that this number doesn't have any validity on its own.

Let's talk techniques. You can measure cyclomatic complexity; I'm not
aware of any RPG tools that do it.

Recognizing that I'm giving comfort to the enemy, a less-unreasonable
approach is to pick a technique to parse your source code, analyze all
your programs using that one approach, and then rank the results. You
can count subroutines, procedures, arrays, pointers, called programs,
variables, length of the variables' names, REGEX's, files/tables,
GoTo's, SQL statements, and built-in functions. Other factors might
include development time and how long it took to get from the initial
version to the final production version (more complex programs will
invariably take longer). In-line comments, although not executable,
can be included in the line counts (but many comments have zero
value). Assign a weight to each factor and you'll get a number for a
program. Do all your programs and you can then identify program
complexity by ranking them. You might consider assigning multiple
complexity rankings. When you review your results, you might adjust your weights to make the programs you categorize as "complex"
show high in the complexity ranking. If you work this right, you can
jack the results to satisify your goals.

One potential problem: dead (unused/unreferenced) code will skew your
results.

A few lines of code from one of my service programs:
theta = lon1 - lon2;
Looks easy, right? But "theta" as a meaningful variable name?
How about this?
dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1))
*
cos(deg2rad(lat2)) * cos(deg2rad(theta)); Does this code make this a
complex program?

Here's an example of why attempting to measure program complexity is a
fool's errand. Take a look at APL, the programming language invented
by IBM's Ken Iverson. In APL, here's how you compute standard deviation:

SD←((+/((X - AV←(T←+/X)÷⍴X)*2))÷⍴X)*0.5

Does the fact this "program" is one line mean the program is simple or
complex? You can do this easily with SQL; it's a bit of code in RPG.

Here's how you compute Pick-6 lottery numbers in APL:

x[⍋x←6?40]

SYMBOLS? Good luck assigning a complexity ranking to a symbol. I
took an internal IBM class in APL back in the 70's and the instructor
noted many APL programmers couldn't understand other programmers' APL
coding. APL makes my hair hurt and I have a full head of hair.

My point: language specifics and coding style greatly affect perceived
program complexity.

Have fun with this...

On Tue, Jun 27, 2023 at 9:16 AM tim ken <timk2574@xxxxxxxxx> wrote:

Hi,

Is there any SQL query which could provide lines of code used
inside all the programs and all the associated modules used in the
entire IBM i system?

Also in this SQL query can we know how many lines are commented and
how many lines are actual program and module code separately in
different columns against each program and it's associated module name ?

Thanks a lot...
--
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://li/
sts.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7Cma
tt.olson%40arrt.org%7Cef10929142aa48f6882308db789cad35%7C51c99a9cbb9
a4dc7ac91464d50cc8d1f%7C1%7C0%7C638236386576331185%7CUnknown%7CTWFpb
GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6M
n0%3D%7C3000%7C%7C%7C&sdata=3t%2BBdz9qyTMX%2B7Y3jmuHe7mLQ9C1ywhg0LQU
ANMkPII%3D&reserved=0 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://list/
s.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C01%7Cmatt.o
lson%40arrt.org%7Cef10929142aa48f6882308db789cad35%7C51c99a9cbb9a4dc7a
c91464d50cc8d1f%7C1%7C0%7C638236386576331185%7CUnknown%7CTWFpbGZsb3d8e
yJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C30
00%7C%7C%7C&sdata=3t%2BBdz9qyTMX%2B7Y3jmuHe7mLQ9C1ywhg0LQUANMkPII%3D&r
eserved=0 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.