• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 484
  • Last Modified:

DB2: Developers need data back from DB2 in increments (pages), need working example.

Hi Techies--
In the SQL Server world, the pattern has been:

CREATE PROCEDURE dbo.GetPatternsByCategoryTypeValuesPaged
(  
    @Page int,               -- // which page do you want to view?
    @RecsPerPage int,  -- // how many records are on a page?
    @styleCategory varchar(100),
    @sizeCategory varchar(100)
.
.
.
-- temp table to deal with paging
    CREATE TABLE #TempItems
    (  ID int identity,
       assemblyNumber varchar(15),
      .
      .
      .
-- then the math

DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT *,
     MoreRecords =
      (
        SELECT COUNT(*)
         FROM #TempItems TI
         WHERE TI.ID >= @LastRec
      )
 FROM #TempItems
 WHERE ID > @FirstRec AND ID < @LastRec

.
.
.


What's the solution for this in the DB2 universe?
0
Paula DiTallo
Asked:
Paula DiTallo
  • 8
  • 7
  • 3
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ditallop,

You could write something similar in DB2 SQL.  They're not that much different.

But why would you want to?  If you need to handle part of the table and commit the results, just read from a cursor and issue a COMMIT at regular intervals.



Kent
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

I usually use a WITH clause to number the rows and just select the specific rows I want.

e.g.

with numbered_rows as (
  select someColumns,
         row_number() over () as rownumber
    from MyTable
)
select someColumns,
       rownumber
  from numbered_rows
 where rownumber between 5 and 10

HTH,
DaveSlash

Open in new window

0
 
Paula DiTalloIntegration developerAuthor Commented:
Dave,

>> where rownumber between 5 and 10 <<


Forgive my confusion/ignorance if this next question seems too remedial. In the sproc I wrote in t-sql, I was getting the records per page as a variable. As far as I know from the develpment side of the house, that will still be true (READ: This sproc accommodates more than one front-end web app with different display requirements). When I convert this over to DB2, can these rownumbers be variables, or is it necessary that they are static?

For a closer look -- here's how I'm deciding what to return today--where @Page and @RecsPerPage are incoming values from one of the applications.

DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
SELECT *,
     MoreRecords =
      (
        SELECT COUNT(*)
         FROM #TempItems TI
         WHERE TI.ID >= @LastRec
      )
 FROM #TempItems
 WHERE ID > @FirstRec AND ID < @LastRec

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Kent OlsenData Warehouse Architect / DBACommented:
I completely misread that procedure.   (Doing that a lot lately....)

Dave seems to be on it.  :)

You can do the same thing in DB2.  Use the Row_number () function to give a unique value to each row in the result set.  Then just select the desired rows based on the values that you pass to the procedure.


0
 
Dave FordSoftware Developer / Database AdministratorCommented:

If you so desire, you could absolutely wrap the the select in a stored procedure and just pass in the row-numbers you're looking for.

Alternately, you don't realy even need to have a stored procedure. You could just fill in the start and end values and issue the query. (I just used 5 and 10 for demonstration purposes.)

HTH,
DaveSlash
0
 
Paula DiTalloIntegration developerAuthor Commented:
ok, i just tried this using IBM Data Studio:

WITH numbered_rows AS (
  SELECT          
        PRCNM
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
 SELECT
        PRCNM
  FROM EDSTST.PRPMS
  WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
 FROM numbered_rows
  WHERE rownumber BETWEEN 5 AND 10


I get a syntax error that reads: This SQL syntax is either invalid or is not currently supported. Some SQL builder functions have been disabled. To enable these functions, change the syntax. I'm likely confused on how to use this.

The base statement:

SELECT PRCNM
  FROM EDSTST.PRPMS
  WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')

returns over 500 rows.

0
 
Paula DiTalloIntegration developerAuthor Commented:

...also... I did try this:

WITH numbered_rows AS (
  SELECT          
        PRCNM
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
 SELECT
        PRCNM
  WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
 FROM numbered_rows
  WHERE rownumber BETWEEN 5 AND 10
 

... same syntax error...
0
 
Kent OlsenData Warehouse Architect / DBACommented:
The WITH syntax needs to declare the passed variable names (just like a stored procedure or function).

Try this one:


WITH numbered_rows (PRCNM, ROWNUMBER)
AS
 (
  SELECT          
        PRCNM
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
 SELECT
        PRCNM
  FROM EDSTST.PRPMS
  WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
 FROM numbered_rows
  WHERE rownumber BETWEEN 5 AND 10
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Oops.

You also want to select from the CTE that you just created.

WITH numbered_rows (PRCNM, PRL04, ROWNUMBER)
AS
 (
  SELECT          
        PRCNM, PRL04,
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
SELECT
    PRCNM
FROM numbered_rows
WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
   AND rownumber BETWEEN 5 AND 10
0
 
Paula DiTalloIntegration developerAuthor Commented:
... and as a last ditch effort... this:

WITH numbered_rows AS (
  SELECT          
        PRCNM,
        PRL04
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
 SELECT
        PRCNM
  WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
 FROM numbered_rows
  WHERE rownumber BETWEEN 5 AND 10
 

... in this one I finally caught on that I can't do a where clause from the temp table construct numbered_rows if I did not collect it.

...but still, the syntax error appears!:-\
0
 
Kent OlsenData Warehouse Architect / DBACommented:

Try the SQL above.  If nothing else, we'll move the error.  :)

0
 
Paula DiTalloIntegration developerAuthor Commented:
...and yet one more hopeful.. but still failed try with:

WITH numbered_rows AS (
  SELECT          
        PRCNM,
        PRL04
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
 SELECT
        PRCNM,
        PRL04
  WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
 FROM numbered_rows
  WHERE rownumber BETWEEN 5 AND 10

0
 
Paula DiTalloIntegration developerAuthor Commented:

KDO,

The error did move--but in an odd way. Suddenly, DB2 is unable to find table EDSTST.PRPMS. It lies! I am looking right at it--and selecting from it!;-)

The statement I issued was this:

WITH numbered_rows (PRCNM, PRL04, ROWNUMBER)
AS
 (
  SELECT          
        PRCNM, PRL04,
         row_number() over () as rownumber
     FROM EDSTST.PRPMS
)
SELECT
    PRCNM
FROM numbered_rows
WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
   AND rownumber BETWEEN 5 AND 10
0
 
Kent OlsenData Warehouse Architect / DBACommented:
>> It lies! I am looking right at it--and selecting from it!;-)

I like that.   :)

What version of DB2 (and O/S) are you running?  CTEs have supported for quite a while.

Also can you cut/paste the sub-select in the CTE into your editor and run it?  (Don't type it, copy/paste in case there's a hidden/illegal character.)

And you might want to delete the FROM line and retype it.  Silly hidden characters in some editors have bitten me more than once.


Kent
0
 
Paula DiTalloIntegration developerAuthor Commented:

Kent,

I'm not sure but I think on the iSeries v of DB2 it follows the OS number (in this instance, V5R4)--none of my old Unix tricks are working!;-)

I did try altering the statement a bit--this iteration actually works!

SELECT PRCNM FROM
         ( SELECT ROW_NUMBER() OVER (ORDER BY PRCNM ASC) AS ROWNUM, emp.*
            FROM EDSTST.PRPMS emp) as subsel WHERE subsel.ROWNUM BETWEEN 10000 AND 10020



Thanks so much for your help!:-)
0
 
Paula DiTalloIntegration developerAuthor Commented:
Thanks Kent and Dave.  I added myself to the solution because of the syntax change to deal with the iSeries version.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

That's certainly weird, but I'm glad you got it to work. Since I, too, use DB2 on an iSeries, I know for a fact that the WITH syntax was valid at V5R4. I'm not sure why it didn't work for you.

For posterity's sake, I would try a "simple case" WITH clause just to see if it's supported for you.


with whatever as (
  select something
    from sometable
)
select *
  from whatever

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ditallop,

Dave's our pretty much definitive resource on DB2 on the AS/400.  If he thinks it'll work, then there is, at most, something very subtly wrong with the SQL.

On LUW and the Z/OS versions (all 3 versions are different code bases) the syntax above should work fine.  The only thing that I see that's different in what you got to work is that you didn't take a default in the OVER () clause.  With that in mind, try the query below.  It's just like the one above, but an order by clause is passed to row_number().

WITH numbered_rows (PRCNM, PRL04, ROWNUMBER)
AS
 (
  SELECT          
        PRCNM, PRL04,
         row_number() over (ORDER BY PRCNM ASC) as rownumber
     FROM EDSTST.PRPMS
)
SELECT
    PRCNM
FROM numbered_rows
WHERE PRL04 IS NOT NULL AND PRL04 IN ('049', '051', '052')
   AND rownumber BETWEEN 5 AND 10



Good Luck,
Kent
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now