referential integrity in as400

Any field in a table that is declared a foreign key can contain only values from a parent table's primary key.
http://en.wikipedia.org/wiki/Referential_integrity

Add PF Constraint (ADDPFCST)

The Add Physical File Constraint (ADDPFCST) command can be used to add constraint relationships to a specified physical file. The four types of constraint relationships that you can add are referential constraints, unique constraints, primary key constraints and check constraints. All constraints are defined at the file level.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fcl%2Faddpfcst.htm 

http://itknowledgeexchange.techtarget.com/itanswers/cpf4328-file-constraint-with-restrict-rules/


video tutorial(i am not good in English but it helpful to you)

ovrdbf command in as400

OVRDBF; it is used to set the default member of fhysical file.
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/cl/ovrdbf.htm

DLTOVR: used to delete the effect of ovrdbf command.

RMVM : remove member
RNMM: rename members

example. video

ADDPFM in as 400

The Add Physical File Member (ADDPFM) command adds a named member to the specified physical file, which must already exist on the system. A member must be added to the physical file before the file can have data stored in it. The first member of a file can be added by entering an ADDPFM command or by specifying a member name in the MBR parameter of the Create Physical File (CRTPF) command. To add other members to the file, use the ADDPFM command to specify each one.
http://publib.boulder.ibm.com/html/as400/v4r5/ic2931/info/cl/addpfm.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fcl%2Faddpfm.htm
-------------------------------
example.
in this example my pf name is pf005

Change Physical File (CHGPF) command

The Change Physical File (CHGPF) command's life purpose is to change the attributes of a physical file and all its members. The Change Physical File command (CHGPF) can be run either through a 5250 terminal session (green-screen) or through iSeries Navigator's (OpsNav) Run Command facility, which allow you to run green-screen commands through a Windows interface. (For more information on using Run Command, see the articles in the Related Stories section at the bottom of this article.)

CHGPF can be used to change several significant file attributes, including the maximum number of members the file can contain, the member size, some performance parameters, and the source file member that the file's field and key descriptions are based on.
http://www.code400.com/forum/showthread.php/8661-Chgpf
http://search400.techtarget.com/tip/Using-CHGPF-to-handle-file-changes-with-ease
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Fcl%2Fchgpf.htm
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/cl/chgpf.htm

REFFLD keyword in db2

The database file name specified on a REFFLD keyword overrides the database file name specified on the REF keyword.
https://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/dds/rbafpmstappa.htm

video

field reference file in as400

A field reference file is a physical file which is used for defining most of your commonly used fields. Then when you need to create / modify a physical file you can use the definitions from the field reference file by using the REF(reference file) and REFFLD (reference field) keywords.
http://itknowledgeexchange.techtarget.com/itanswers/what-is-field-reference-file/
Field Reference Files:
• A physical file that contains no data, but only description of fields.
• It is used as a reference for the field descriptions of other files
Importance of using Field Reference Files
It enforces the naming convention
• Reduces programmer’s work load (define once, use often)
• It defines in one place all data elements used in the application for all users
Naming Conventions
• The file name, record format name, field name cannot be more than 10 character’s
• The first character should be alphabetic
• Field names should be unique in a record format
• Record format names and member names should be unique in a file
• File names should be unique in a library
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fdbp%2Frbafocrref.htm

inserting data INto physical file USING SQL

Start the sql interface
in command prompt type strsql command.
***************************************************************************
                           Work with Members Using PDM                 PUB1    
                                                                               
 File  . . . . . .   MYDDS                                                     
   Library . . . .     CHVRAMESH2           Position to  . . . . .             
                                                                               
 Type options, press Enter.                                                    
  2=Edit         3=Copy  4=Delete 5=Display       6=Print     7=Rename         
  8=Display description  9=Save  13=Change text  14=Compile  15=Create module...
                                                                               
 Opt  Member      Type        Text                                             
      PF001       PF          my pf file                                       
      STUDENT     PF          student physical file                            
      STUDENT1    PF          student physical file unique                     
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                         Bottom
 Parameters or command                                                         
 ===> STRSQL                                                                   
 F3=Exit          F4=Prompt             F5=Refresh            F6=Create        
 F9=Retrieve      F10=Command entry     F23=More options      F24=More keys    
****************************************************************88

this screen will open
============================================================
                             Enter SQL Statements                            
                                                                             
Type SQL statement, press Enter.                                             
   > SELECT * FROM STUDENT1                                                  
     SELECT statement run complete.                                          
   > SELECT * FROM STUDENT                                                   
     SELECT statement run complete.                                          
     Session was saved and started again.                                    
     Current connection is to relational database PUB1.                      
===>   ----------------------------------------
------------------------------------------------------                                                                      
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                       Bottom
F3=Exit   F4=Prompt   F6=Insert line   F9=Retrieve   F10=Copy line           
F12=Cancel            F13=Services     F24=More keys                          
=====================================================
insert into student1 values('ramesh','ece','5678',9898987654)
press enter
 > insert into student1 values('ramesh','ece','5678',9898987654)   
   1 rows inserted in STUDENT1 in CHVRAMESH2.                      
=====================
now type select * from student1

the out put is
                                  Display Data                                 
                                              Data width . . . . . . :      62 
 Position to line  . . . . .              Shift to column  . . . . . .         
 ....+....1....+....2....+....3....+....4....+....5....+....6..                
 NAME                    BRANCH      CARDNO              PHONE                 
 ramesh                  cse         23413       9,999,999,999                 
 ramesh                  it          54321       9,999,999,996                 
 ramesh                  ece         5678        9,898,987,654                 
 ********  End of data  ********                                                

unique keyword physical file as400

Use this file-level keyword to specify that records with duplicate key values are not allowed within a member of this physical or logical file.
ref:
http://publib.boulder.ibm.com/infocenter/iadthelp/v6r0/index.jsp?topic=/com.ibm.etools.iseries.langref2.doc/rzakbmsttduniqu.htm


example:
create one physical file
----------------------------
 Columns . . . :    1  71            Edit                      CHVRAMESH2/MYDDS
 SEU==>                                                                STUDENT1
 FMT PF .....A..........T.Name++++++RLen++TDpB......Functions++++++++++++++++++
        *************** Beginning of data *************************************
0001.00      A                                      UNIQUE                     
0002.00      A          R STUDENTR                                             
0003.00                   NAME          22A                                    
0004.00                   BRANCH        10A                                    
0005.00                   CARDNO        10A                                    
0006.00                   PHONE         10S 0                                  
0007.00                 K PHONE                                                
        ****************** End of data ****************************************

in this
phone is our key field.
so we are try to insert duplicate value for phone what happned?
---------------------
 WORK WITH DATA IN A FILE                       Mode . . . . :   ENTRY       
 Format . . . . :   STUDENTR                    File . . . . :   STUDENT1    
                                                                             
 PHONE:  9999999999                                                          
 NAME:   ramesh                                                              
 BRANCH: it                                                                  
 CARDNO: 54321                                                               
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
 F3=Exit                 F5=Refresh               F6=Select format           
 F9=Insert               F10=Entry                F11=Change                 
 File STUDENT1 cannot have a duplicate key.                                  

PHYSICAL FILE MAINTENANCE COMMANDS in as400

CHGPF	Change physical file
CLRPFM	Clear physical file member
CPYF	Copy file
DLTF	Delete File
DSPPFM	Display Physical File Member
RMVM	Remove Member
DSPPFD	Display Physical file Description

keyword levels in physical file

   

--

KEYWORDS in physical file

keywords are typed in positions 45 through 80

 ABSVAL (Absolute Value) keyword for physical and logical files
ALIAS (Alternative Name) keyword for physical and logical files

ALTSEQ (Alternative Collating Sequence) keyword for physical and logical files
ALWNULL (Allow Null Value) keyword—physical files only
CCSID (Coded Character Set Identifier) keyword for physical and logical files
CHECK (Check) keyword for physical and logical files
CHKMSGID (Check Message Identifier) keyword for physical and logical files
CMP (Comparison) keyword for physical and logical files
COLHDG (Column Heading) keyword for physical and logical files
Topic openCOMP (Comparison) keyword for physical and logical files

    Specify COMP at the field level
    Specify COMP at the select- or omit-field level


DATFMT (Date Format) keyword for physical and logical files
DATSEP (Date Separator) keyword for physical and logical files
DESCEND (Descend) keyword for physical and logical files
DFT (Default) keyword—physical files only
DIGIT (Digit) keyword for physical and logical files

EDTCDE (Edit Code) and EDTWRD (Edit Word) keywords for physical and logical files
FCFO (First-Changed First-Out) keyword for physical and logical files
FIFO (First-In First-Out) keyword for physical and logical files
FLTPCN (Floating-Point Precision) keyword for physical and logical files
FORMAT (Format) keyword for physical and logical files

LIFO (Last-In First-Out) keyword for physical and logical files
NOALTSEQ (No Alternative Collating Sequence) keyword for physical and logical files

Topic openRANGE (Range) keyword for physical and logical files

    Specify RANGE at the field level
    Specify RANGE at the select or omit-field level

REF (Reference) keyword—physical files only

REFFLD (Referenced Field) keyword—physical files only
REFSHIFT (Reference Shift) keyword for physical and logical files

SIGNED (Signed) keyword for physical and logical files

TEXT (Text) keyword for physical and logical files
TIMFMT (Time Format) keyword for physical and logical files
TIMSEP (Time Separator) keyword for physical and logical files

UNIQUE (Unique) keyword for physical and logical files
UNSIGNED (Unsigned) keyword for physical and logical files
Topic openVALUES (Values) keyword for physical and logical files

    Specify VALUES at the field level
    Specify VALUES at the select or omit-field level

VARLEN (Variable-Length Field) keyword for physical and logical files
ZONE (Zone) keyword for physical and logical files


http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.ibm.etools.iseries.langref2.doc/rzakb/rzakbmstlpfile.htm
------------------------------------------------------------------
ABSVAL
ALIAS

ALTSEQ
ALWNULL
CCSID
CHECK
CHKMSGID
CMP
COLHDG
COMP

DATFMT
DATSEP
DESCEND
DFT
DIGIT

EDTCDE
EDTWRD
FCFO
FIFO
FLTPCN
FORMAT
LIFO
NOALTSEQ
RANGE
REF
REFFLD
REFSHIFT

SIGNED

TEXT
TIMFMT
TIMSEP

UNIQUE
UNSIGNED
VALUES
VARLEN
ZONE

how to see the content of physical file

type runqry *n (filename) in command prompt

-----------
press enter

inserting data to physical file

type upddta and press f4

-----------------
press enter

-------------------
enter the values
and press f3

creating the physical file in as 400

Creation of physical file:
Type wrkmbrpdm in command line.

the fallowing scree is open










 
----------------------------

press f6 for creating new number(pf)
give
member name and type .
in this i creating for student pf.

---------------
---------------------------------------------------
press enter
you get this screen

and type ippf(input prompt for physical file)
press enter

-----------------------------

in this give record format name.
in 17th position

-----------------------
-----------

------------
------
type the required fields
and enter file (for save and exit the seu)
and press enter

-----------

give option 14 for compaliation