1

I have troubling issue where I want to only select the first 2 chunks of every dbspace created.

query used:

SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2

Output:

dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

dbsnum  1
chknum  965
name    rootdbs
fname   /dev/informix/chunk1280

dbsnum  1
chknum  1099
name    rootdbs
fname   /dev/informix/chunk1281

What I want is:

dbsnum  1
chknum  1
name    rootdbs
fname   /dev/informix/rootdbs01

dbsnum  1
chknum  344
name    rootdbs
fname   /dev/informix/chunk1081

I am trying to put logic together to select the first 2 chunks created for example from each dbspace that exists. rootdbs has a total of 4 chunks I only want the first 2.

I can achieve this using a korn shell script:

for i in `echo "output to pipe cat without headings select unique(dbsnum) from sysdbspaces" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
do
fname=`echo "output to pipe cat without headings select a.fname from (select 
first 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
AND a.dbsnum = '$i'
--AND a.is_temp = 1
GROUP BY
1,2,3,4
ORDER BY 1,2) a" | dbaccess sysmaster 2> /dev/null | sed '/^$/d'`
echo "$fname"
done 
2
  • 1
    select the first 2 chunks For the term "first" to exist you must specify definite ordering expression which provides rows uniqueness. rootdbs has a total of 4 chunks I only want the first 2. SELECT TOP 2 ... must solve.
    – Akina
    Feb 9, 2021 at 8:15
  • The chknum values increase as chunks are added — the order required is the two smallest chunk numbers in each dbspace. It isn't clear what should happen if a dbspace has only one chunk — maybe the one chunk should be listed, maybe the dbspace should not be listed. Feb 9, 2021 at 17:56

3 Answers 3

1

I would note that it is possible in a dynamic server environment for the second and later chunks to not be in order by chknum with each other or even for the chknums to be greater than the first chunk in the dbspace. If you drop a chunk or a whole dbspace that contains chunks with chknums less than an existing chunk in other remaining dbspaces adding a new chunk to an existing dbspace will add the new chunk with a lower chunk number than the existing chunks in the existing dbspaces. So, if you really want the chunks in the order that they were added to the dbspace you will have to follow the nxtchk link in each chunk's syschunks record. You can use the Informix hierarchical query feature to get the results that you want:

select distinct dbsnum, chknum, nxchunk  
from syschktab  
where level <= 2  
connect by prior chknum = nxchunk  
order by dbsnum;

dbsnum chknum nxchunk 

     1      1       0
     2      2       0
     3      3       0
     4      4      13
     4     13       0
     5      5       0
     6      6      14
     6     14       0
     7      7      10
     7     10       0
     8      8       0
     9      9       0
    10     11      12
    10     12       0

The DISTINCT (or UNIQUE) keyword is needed to prevent, for example, chunk 13 from linking back to chunk 4.

To include the dbspace name and the chunk path, use the query above as a derived table and join it back to sysdbspaces and syschunks by dbsnum and chknum respectively:

select d.dbsnum, c.chknum, name, fname
from sysdbspaces d,
    syschunks c,
    (
        select distinct dbsnum, chknum, nxchunk
        from syschktab
        where level <= 2
        connect by prior chknum = nxchunk
        order by dbsnum
    ) as j
where d.dbsnum = j.dbsnum
  and c.chknum = j.chknum
order by dbsnum;

dbsnum  1
chknum  1
name    rootdbs
fname   /opt/informix/chunks/rootdbs.chk.001

dbsnum  2
chknum  2
name    llogspace
fname   /opt/informix/chunks/llogspace.chk.001

dbsnum  3
chknum  3
name    plogspace
fname   /opt/informix/chunks/plogspace.chk.001

dbsnum  4
chknum  4
name    datadbs_1
fname   /opt/informix/chunks/datadbs_1.chk.001

dbsnum  4
chknum  13
name    datadbs_1
fname   /opt/informix/chunks/chunk_datadbs_1_0002

dbsnum  5
chknum  5
name    indexdbs_1
fname   /opt/informix/chunks/indexdbs_1.chk.001

dbsnum  6
chknum  6
name    tempdbs
fname   /opt/informix/chunks/tempdbs.chk.001

dbsnum  6
chknum  14
name    tempdbs
fname   /opt/informix/chunks/elendil_tempdbs_p_1

dbsnum  7
chknum  7
name    sbspace
fname   /opt/informix/chunks/sbspace.chk.001

dbsnum  7
chknum  10
name    sbspace
fname   /opt/informix/chunks/elendil_sbspace_p_1

dbsnum  8
chknum  8
name    cdrspace
fname   /opt/informix/chunks/cdrspace.chk.001

dbsnum  9
chknum  9
name    indexdbs
fname   /opt/informix/chunks/indexdbs.chk.001

dbsnum  10
chknum  11
name    datadbs_2
fname   /opt/informix/chunks/datadbs_2.chk.001

dbsnum  10
chknum  12
name    datadbs_2
fname   /opt/informix/chunks/MasterPool.001
0

As Akina mentioned in the comments, if there's only 4 records total, and you want the top 2, and you're already ordering how you'd want them then you just need to add the LIMIT clause to your query like so:

SELECT
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2
LIMIT 2;

The above is for Informix syntax and assumes you accidentally tagged the question as "sql-server" by mistake (because that's the Microsoft SQL Server tag). Otherwise if you want the syntax for Microsoft SQL Server, this is it:

SELECT TOP 2
a.dbsnum,
b.chknum,
a.name,
b.fname
FROM
sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
GROUP BY
1,2,3,4
ORDER BY 1,2
3
  • I think the desired output is more than 'the top 2' — it is the "top 2 for each dbspace (dbsnum)". So, in the example data, there are 4 chunks for the rootdbs dbspace, and the two smallest chunk numbers are of interest, but if there was another dbspace, the output would include the 'first two chunks' for that dbspace too. There are typically a number of dbspaces in an Informix instance. Judging from the numbering, there are quite a lot of dbspaces and chunks within dbspaces in this instance. Feb 9, 2021 at 17:52
  • Correct. What has been provided as a solution will not work, because there is more than one name or in this case dbspaces and I only want the the 1st two created chunks of each dbspace Feb 10, 2021 at 14:23
  • @ChristopherKarsten Sorry I didn't understand that until now. In that case you probably just need to PARTITION by dbspace with the same ORDER BY clause applied. I'll try to update this answer when I get a chance tonight with a working example.
    – J.D.
    Feb 10, 2021 at 15:14
0
select * from (SELECT
    row_number() over (partition by 1,2,3,4 order by 1,2) as RowNo,
    a.dbsnum,
    b.chknum,
    a.name,
    b.fname
    FROM
    sysdbspaces a, syschunks b where a.dbsnum = b.dbsnum
    GROUP BY
    1,2,3,4) as innerSelect
where RowNo <=2
3
  • When run against a Informix 14.10 server (after adding a comma after RowNo), I get error SQL -321: Cannot group by aggregate column.. Feb 9, 2021 at 17:46
  • What is the purpose of the group by clause? Feb 10, 2021 at 6:15
  • my bad, was copypasting/writing on mobile. Remove the group by clause of course Feb 10, 2021 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.