Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
www.cozTools.com/qryfile
Chicagoland
DB2 Long File Name Use
has no ratings.
Published: 08 Jul 2015
Revised: 08 Jul 2015 - 3205 days ago
Last viewed on: 16 Apr 2024 (3576 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

DB2 Long File Name Use Published by: Bob Cozzi on 08 Jul 2015 view comments(2)

Is anyone using long file names on their system?

Here's a developerworks article in case you are unfamiliar with long file names.

If so, why and what for?

If not, why not?

I wrote a little CL command that can be used to create SOURCE FILES with LONG NAMES. (not sure why I'm using all caps there???) Why a long source file name? Because I use source files for logging information and storing SQL statements, and I often want to create a file that is unique but not "temp". With source files, typically you want to create utilize multiple members. Sadly you still only have the 10-character name limit for member names. Here's an example:

CRTSRCLONG SRCFILE(COZZILIB/midrangeNews_source_code_examples) TEXT('See file name ")

You have to utilize the SYSTEM NAME of this file when issuing an ADDPFM command. Strange how there are no APIs for any of the following tasks:

  • Create Source File
  • Add member
  • Remove member

Probably many others I haven't thought of yet.

Anyway, here's the command definition source:

 CRTSRCLONG: CMD        PROMPT('CRTSRCPF with Long Name')

             PARM       KWD(FILE) TYPE(QUAL1) MIN(1) PROMPT('Source +
                          file name: max(128)')
             PARM       KWD(RCDLEN) TYPE(*INT4) DFT(112) RANGE(13 +
                          32766) EXPR(*YES) PROMPT('Record length')
             PARM       KWD(TEXT) TYPE(*CHAR) LEN(50) DFT(*BLANK) +
                          SPCVAL((*BLANK)) EXPR(*YES) VARY(*YES) +
                          PROMPT('Text ''description''')
 QUAL1:      QUAL       TYPE(*NAME) LEN(128) MIN(1) EXPR(*YES) +
                          VARY(*YES) INLPMTLEN(32)
             QUAL       TYPE(*NAME) LEN(10) DFT(*CURLIB) +
                          SPCVAL((*CURLIB)) EXPR(*YES) +
                          PROMPT('Library')

And here is the CL source (requires IBM i v7.1 or later)

 CRTSRCLONG: PGM        PARM(&SRCF &RCDLEN &TEXTDESC)
             DCL        VAR(&SRCF)    TYPE(*CHAR) LEN(140)
             DCL        VAR(&RCDLEN)   TYPE(*INT) LEN(4)
             DCL        VAR(&TEXTDESC) TYPE(*CHAR) LEN(52)

   /****************************************************************/
   /** Copyright (c) 2015 by R. Cozzi, Jr. All rights reserved.   **/
   /** This program creates a Source File in the target library   **/
   /** and then renames the object to the user-supplied long      **/
   /** file name. If the file name is <= 10, it only creates      **/
   /** the source file with the short name and avoids renaming.   **/
   /****************************************************************/

             DCL        VAR(&SRCNAMELEN) TYPE(*INT) STG(*DEFINED) +
                          LEN(2) DEFVAR(&SRCF)
             DCL        VAR(&SRCFILE) TYPE(*CHAR) STG(*DEFINED) +
                          LEN(128) DEFVAR(&SRCF 3)
             DCL        VAR(&SRCLIB) TYPE(*CHAR) STG(*DEFINED) +
                          LEN(10) DEFVAR(&SRCF 131)
             DCL        VAR(&TEXTLEN) TYPE(*INT) STG(*DEFINED) +
                          LEN(2) DEFVAR(&TEXTDESC)
             DCL        VAR(&TEXT) TYPE(*CHAR) STG(*DEFINED) +
                          LEN(50) DEFVAR(&TEXTDESC 3)

             DCL        VAR(&SRCNAME) TYPE(*CHAR) LEN(10)
             DCL        VAR(&TXTDSC)  TYPE(*CHAR) LEN(50)
             DCL        VAR(&JOBNBR) TYPE(*CHAR) LEN(6)
             DCL        VAR(&CURLIB) TYPE(*CHAR) LEN(10)
             DCL        VAR(&STMT)    TYPE(*CHAR) LEN(255)

             DCL        VAR(&MSGKEY) TYPE(*CHAR) LEN(4)
             DCL        VAR(&MSGTYPES) TYPE(*CHAR) LEN(40)
             DCL        VAR(&TYPECOUNT) TYPE(*INT) LEN(4)
             DCL        VAR(&TOPGMQ) TYPE(*CHAR) LEN(10)
             DCL        VAR(&TOCSE) TYPE(*INT) LEN(4)
             DCL        VAR(&EC) TYPE(*CHAR) LEN(16)


             MONMSG     MSGID(CPF0000)

             IF (&TEXTLEN > 0 *AND %SST(&TEXT 1 &TEXTLEN) *NE '*BLANK') THEN(DO)
               CHGVAR &TXTDSC %SST(&TEXT 1 &TEXTLEN)
             ENDDO
             ELSE DO
               CHGVAR     VAR(&TXTDSC) VALUE('*N')
             ENDDO

SHORTNAME:   IF (&SRCNAMELEN <= 10) THEN(DO)
               CRTSRCPF   FILE(&SRCLIB/%SST(&SRCFILE 1 &SRCNAMELEN)) +
                            RCDLEN(&RCDLEN) TEXT(&TXTDSC)
               RETURN
             ENDDO

 LONGNAME:   RTVJOBA    NBR(&JOBNBR) CURLIB(&CURLIB)
             CHGVAR     VAR(&SRCNAME) VALUE('TMP_' *CAT &JOBNBR)
             CRTSRCPF   FILE(&SRCLIB/&SRCNAME) RCDLEN(&RCDLEN) +
                          TEXT(&TXTDSC)
             IF (&SRCLIB = '*CURLIB') THEN(DO)
               CHGVAR     VAR(&SRCLIB) VALUE(&CURLIB)
             ENDDO

             CHGVAR     VAR(&STMT) VALUE('RENAME TABLE' *BCAT +
                          &SRCLIB *TCAT '/' *CAT &SRCNAME *BCAT +
                          'TO' *BCAT %SST(&SRCFILE 1 &SRCNAMELEN))

             /* The following requires a later TR refresh of IBM i v7.1  */
             /* If you do not have it on your system, try out the        */
             /* SQL Query File product http://www.cozTools.com/qryFile   */
             /* It runs on V5R4 and later.                               */

 RUNSQL:     RUNSQL     SQL(&STMT) COMMIT(*NONE) NAMING(*SYS)

             CHGVAR     VAR(&MSGTYPES) VALUE('*INFO     *COMP     *DIAG')
             CHGVAR     VAR(&TYPECOUNT) VALUE(3)
             CHGVAR     VAR(&TOCSE) VALUE(1)
             CHGVAR     VAR(&TOPGMQ) VALUE('*PGMBDY')
             CHGVAR     VAR(&EC) VALUE(X'0000000000000000')
             CALL       PGM(QMHMOVPM) PARM(&MSGKEY &MSGTYPES +
                          &TYPECOUNT &TOPGMQ &TOCSE &EC)

 ENDPGM:     ENDPGM

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: starbuck5250
Premium member *
Comment on: DB2 Long File Name Use
Posted: 8 years 9 months 9 days 21 hours 3 minutes ago

Yes, we use long SQL names; not just table and views, but stored procedures and functions, too.  Only for brand new development.  By brand new, I mean that the database and application are designed for, and intended to be used with SQL as opposed to RPG F specs.  That's the 'what for'.  As for the 'why', well that's because 10 characters is too small.

There's a really big caveat when using SQL-defined tables, and it relates to the internal database cross reference used by DB2 to map the short object name (every file has one) to the long SQL table name.  If you use CPYF...CRTFILE(*YES) to make a backup, it will fall over.

create table buck.sales_transactions (customerID char(10));

This creates a table called SALES00001.  This is what you'd put on an F-spec.  Let's make a copy of it:

cpyf SALES00001 buck/salesback crtfile(*yes)    
Alternative name for file SALESBACK not allowed.
Error creating file SALESBACK in library BUCK.  
Copy command ended because of error.           

That's because schema (library) BUCK already has a table named SALES_TRANSACTIONS.  In order to make a copy, we need to change not only the short name, but the SQL name too.  That can't be done with CPYF - it requires SQL:

create table buck.sales_transactions_backup as (select * from  sales_transactions) with data;

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: DB2 Long File Name Use
Posted: 8 years 9 months 9 days 17 hours 4 minutes ago

Hi Buck, thanks. Note that I've updated the original post with some new content.