ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Record Locking in Embedded SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Record Locking in Embedded SQL

    Hey Buddies

    I have opened a file File1 for Update and I use an embedded SQL select to retrieve records from File1.Will the SQL select statement will lock those records which have been retrieved?


    If yes how to select records without the lock on the file because I do not update all the records that have been retrieved.

    Thanks in advance.

    Regards
    Nanda.

  • #2
    Re: Record Locking in Embedded SQL

    use: for fetch only on your sql statement
    Regards

    Kit
    http://www.ecofitonline.com
    DeskfIT - ChangefIT - XrefIT
    ___________________________________
    There are only 3 kinds of people -
    Those that can count and those that can't.

    Comment


    • #3
      Re: Record Locking in Embedded SQL

      Thanks Kit for the reply.


      So I need to use like FETCH ONLY NEXT FROM CURSOR1?


      Is there anyway to declare Cursor as read only?

      Thank You

      Regards
      Nanda.

      Comment


      • #4
        Re: Record Locking in Embedded SQL

        --or--

        Code:
        SELECT   EMPNO, LASTNAME, SALARY 
        FROM     EMP 
        WHERE    SALARY > 10000.00 
        FOR READ ONLY;
        All my answers were extracted from the "Big Dummy's Guide to the As400"
        and I take no responsibility for any of them.

        www.code400.com

        Comment


        • #5
          Re: Record Locking in Embedded SQL

          Thanks Jamie for ur help

          Comment


          • #6
            Re: Record Locking in Embedded SQL

            Originally posted by jamief View Post
            --or--

            Code:
            SELECT   EMPNO, LASTNAME, SALARY 
            FROM     EMP 
            WHERE    SALARY > 10000.00 
            FOR READ ONLY;


            I've been meaning to ask about this because I have an object locked from a CGI-RPG program and all I am doing is reading from it. I do have "For Read Only" declared and still I lock it.
            Your friends list is empty!

            Comment


            • #7
              Re: Record Locking in Embedded SQL

              I am fairly sure that you don't get the lock if you use a view instead of the table.
              Philippe

              Comment


              • #8
                Re: Record Locking in Embedded SQL

                I switched it to a logical view and now my lock is on the logical!

                I'm doing a very basic ...

                Code:
                select fields
                
                from file
                
                where simple condition
                
                for read only
                Your friends list is empty!

                Comment


                • #9
                  Re: Record Locking in Embedded SQL

                  Is "file" in this example above created by a CREATE VIEW stm ?
                  Philippe

                  Comment


                  • #10
                    Re: Record Locking in Embedded SQL

                    No, it's a LF created with DDS.
                    Your friends list is empty!

                    Comment


                    • #11
                      Re: Record Locking in Embedded SQL

                      What kind of lock are you seeing ? A file opened for update WILL lock any record read, until (1) the record is ypdated, (2) another record is read, or (3) UNLOCK commnd is processed. Any "read" open will NOT lock the file. You may have to UNLOCK the record before SQL can process it.

                      Are you using commitment control or journalling?

                      Comment


                      • #12
                        Re: Record Locking in Embedded SQL

                        Code:
                        Opt   Job          User         Lock      Status          Scope     Thread 
                              CGIDEV2      QTMHHTTP     *SHRRD     HELD           *JOB             
                                                        *SHRRD     HELD           *JOB             
                                                        *SHRRD     HELD           *JOB

                        Here's what I'm doing in the program:

                        Suppose to close cursor at the end of program, also ingore case during comparisons.
                        Code:
                        Exec Sql                                    
                          Set Option closqlcsr = *endmod,           
                                     srtseq = *langidshr;

                        I do this to get an overall record count.
                        Code:
                        // build sql string                                                  
                        sqlString = whereString + groupString + havingString + orderString;  
                                                                                             
                        Exec Sql                                                             
                          Prepare SQL2 from :sqlString ;                                     
                        Exec Sql                                                             
                          Declare C2 Cursor for SQL2;                                        
                        Exec Sql                                                             
                          Open C2;                                                           
                        Exec Sql                                                             
                          Fetch from C2 into :total;                                         
                        Exec Sql                                                             
                          Close C2;

                        main loop of program to spit out records:
                        Code:
                        // build sql string                                                 
                        sqlString = whereString + groupString + havingString + orderString; 
                        
                                                                                            
                        Exec Sql                                                            
                        PREPARE SQL FROM :sqlString;                                        
                                                                                            
                        Exec Sql                                                            
                        Declare C1 Asensitive Scroll Cursor for SQL;                        
                                                                                            
                        Exec Sql                                                            
                        Open C1;                                                            
                                                                                            
                        Exec Sql                                                            
                        Fetch Relative :X from C1 INTO :SQL_DS ;

                        Interesting, the first time thru the program the lock appeared and went away when the program was finished. After that the second run the lock stuck.

                        I'm not doing anything with commitment control or journalling that I'm aware of.
                        Your friends list is empty!

                        Comment


                        • #13
                          Re: Record Locking in Embedded SQL

                          Now I'm frustrated. I thought you pointed out a glitch of mine when you mentioned the lock waiting for another record to be read. I did have a situation where I was jumping out of my DO loop before the EOF because I'm only reading 20 records at a time for the grid. So I put in an extra Close C1.

                          Same results. I've even made sure both times I access the file it's "For Read Only".

                          First time into the CGI-RPG program I see the lock appear and disappear. Second time I run it I get the three locks above.
                          Your friends list is empty!

                          Comment


                          • #14
                            Re: Record Locking in Embedded SQL

                            Build a genuine view (CREATE VIEW) and give it a try.
                            Anyway, if the underlying table is already open for update, using a view would'nt solve the case.
                            Additionally try also
                            Set option = ALWBLK(*ALLREAD)
                            Philippe

                            Comment


                            • #15
                              Re: Record Locking in Embedded SQL

                              The SQL selects, whether reading a view or a LF, are not going to lock any records. The file is going to show a *SHRRD lock anyway, but that is not a record lock. In your post, it looks like locks are held - this is normal. Any record lock must be coming from somewhere else. Where are you seeing a record lock ?

                              Code:
                              Opt   Job          User         Lock      Status          Scope     Thread 
                                    CGIDEV2      QTMHHTTP     *SHRRD     HELD           *JOB             
                                                              *SHRRD     HELD           *JOB             
                                                              *SHRRD     HELD           *JOB

                              Comment

                              Working...
                              X