EzDevInfo.com

Equivalent of LIMIT for DB2

How do you do LIMIT in DB2 for iSeries?

I have a table with more than 50,000 records and I want to return records 0 to 10,000, and records 10,000 to 20,000.

I know in SQL you write LIMIT 0,10000 at the end of the query for 0 to 10,000 and LIMIT 10000,10000 at the end of the query for 10000 to 20,000

So, how is this done in DB2? Whats the code and syntax? (full query example is appreciated)

How do I use DB2 Explain?

How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2?

I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal.

Edit: The answer for me turned out to be "You can't. You don't have and cannot get the access." Don't you love bureaucracy?

How do I connect to a Websphere Datasource with a given JNDI name?

I'm using Websphere Portal 7.0 and creating a portlet with RAD 8.0. My portlet is trying to make a db2 connection to a remote server. I wrote a java program locally to do a basic JDBC connection to the server and get records from a table. The code works fine; however, when I add the code to my portlet as well as the db2jcc4.jar, the connection doesn't work. I'm using the basic:

Connection connection = DriverManager.getConnection("jdbc:db2://server:port/db:user=user;password=pw;");

I figure that using the Websphere datasource is the right way to go. I know the JNDI name for the datasource, but I'm not finding clear cut examples on how to make a connection. Several examples use a DataSource class (I typed this in and this doesn't seem like it comes from a native java package so what import do I use here?) coupled with a Context. I've come across code like:

Context ctx = new InitialContext();
ctx.lookup("jdbc/xxxx");

... Can someone break this down for me?

EDIT 1

I've updated my code per the answers listed. I really think I'm getting closer. Here is my getConnection() method:

private Connection getConnection() throws SQLException {
    javax.naming.InitialContext ctx = null;
    javax.sql.DataSource ds = null;
    System.out.println("Attempting connection..." + DateUtil.now() );
    try {
        ctx = new javax.naming.InitialContext();
        ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/db");
        connection = ds.getConnection();
    } catch (NamingException e) {
        System.out.println("peformanceappraisalstatus: COULDN'T CREATE CONNECTION!");
        e.printStackTrace();
    }       
    System.out.println("connection: " + connection.getClass().getName() + " at " + DateUtil.now());
    return connection;
}

My entire web.xml file looks like:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <display-name>PeformanceAppraisalStatus</display-name>
    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
        <welcome-file>index.htm</welcome-file>
        <welcome-file>index.jsp</welcome-file>
        <welcome-file>default.html</welcome-file>
        <welcome-file>default.htm</welcome-file>
        <welcome-file>default.jsp</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>
        Datasource connection to Db</description>
        <res-ref-name>jdbc/db</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>
</web-app>

I am seeing an error that describes the very thing you guys are telling me Websphere should prompt me to do, but doesn't:

SRVE0169I: Loading Web Module: PeformanceAppraisalStatus.
[8/23/11 18:08:02:166 CDT] 00000009 InjectionProc E   CWNEN0044E: A resource reference binding could not be found for the jdbc/db resource reference, defined for the PeformanceAppraisalStatus component.
[8/23/11 18:08:02:169 CDT] 00000009 InjectionEngi E   CWNEN0011E:  The injection engine failed to process bindings for the metadata.

Yes, I know that I've mispelled performance as peformance throughout the app.

SOLUTION

I was so very close. Here are the missing bits that made it all fall into place:

web.xml:
<resource-ref>      
    <description>
    Datasource connection to db</description>
    <res-ref-name>jdbc/db</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
    <mapped-name>jdbc/db</mapped-name>      
</resource-ref>

ibm-web-bnd.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-bnd 
    xmlns="http://websphere.ibm.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://websphere.ibm.com/xml/ns/javaee http://websphere.ibm.com/xml/ns/javaee/ibm-web-bnd_1_0.xsd"
    version="1.0">

    <virtual-host name="default_host" />


    <resource-ref name="jdbc/db" binding-name="jdbc/mydatasource" />
</web-bnd>

It appears that the ibm-web-bnd.xml file handles the binding between the project resource name and the datasource in websphere. Once I added the line:

<resource-ref name="jdbc/db" binding-name="jdbc/mydatasource" />

Websphere Portal seemed appeased. My code is working and connecting to the database now.

How to check db2 version

How to check db2 version on Z/OS using only SQL commands?

Thanks, Melita

SqlException: DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null

What does sqlcode -302 means? Where do i get the sqlcode definitions?

Please advice

Thanks,

Simple DB2 Query for connection validation

I'm looking for a simple DB2 query that can be used to test if a database connection in pool is still valid. It needs to be a generic query that would execute regardless of which databases exist.

For other database servers, I've used something like 'SELECT 1' or 'SELECT version();'

What would be an equivalent for DB2?

Thanks!

SQL query of multi-member file on AS400

On AS400 in interactive SQL in a 5250 session,

select * from myfile

returns rows from one member only when myfile has more than one member.

How can I get rows from a specific member?

Important: in the end I'd like to do this over JDBC with jt400 so really I want a solution that'll work there.

Thanks.

how do i get the current schema on DB2 if i have a JDBC conneciton?

If I have an instance of a JDBC DB2 connection, how do I get the current schema?

Either a SQL statement would work, or just a JDBC method call.

IsNull function in DB2 SQL?

Is there a performant equivalent to the isnull function for DB2?

Imagine some of our products are internal, so they don't have names:

Select product.id, isnull(product.name, "Internal) 
From product

Might return:

1 Socks 
2 Shoes 
3 Internal 
4 Pants

How to identify DB2 port number

I have to make DB2 connection in java using port number. Is there any command in DB2 or any way that can get the DB2 port number?

I have not used the default port 50000 while making DB2 connection as this port can be changed during DB2 installation. Please suggest any DB2 command or any other alternative.

insert multiple rows into DB2 database

I want to insert multiple rows into a DB2 table. I have a query that looks like this

insert into tableName 
(col1, col2, col3, col4, col5) 
values 
(val1, val2, val3, val4, val5),
(val1, val2, val3, val4, val5),
(val1, val2, val3, val4, val5),
(val1, val2, val3, val4, val5);

This query does't work. I don't know if there is a more syntactically correct way to do this in DB2. But it'd be useful to insert my test data.

Declare a variable in DB2 SQL

Does anyone know how to run the following SQL Server code in DB2?

I am converting SQL Server scripts so that they will run on a DB2 system and am having some problems wrapping my head around the use of variables in DB2.

T-SQL code

This is obviously not the actual code but works well as an example.

DECLARE @INPUT_VALUE INT
SET INPUT_VALUE = 4756

SELECT *
FROM TABLE1
WHERE TABLE1.COLUMN1 = @INPUT_VALUE

Create a copy of a table within the same database DB2

Is there an easy way to copy a table to the same database of course with different name. I tried some of these listed below,

db2 "CREATE TABLE SCHEMA.NEW_TB COPY AS SELECT * FROM SCHEMA.OLD_TB WHERE 1 = 2"

db2 "SELECT INTO SCHEMA.NEW_TB FROM SCHEMA.OLD_TB"

db2 "SELECT * FROM SCHEMA.OLD_TB INSERT INTO SCHEMA.NEW_TB"

None of these worked I am using db2 v9.5

How to AUTO_INCREMENT in db2?

I thought this would be simple, but I can't seem to use AUTO_INCREMENT in my db2 database. I did some searching and people seem to be using "Generated by Default", but this doesn't work for me.

If it helps, here's the table I want to create with the sid being auto incremented.

  create table student(
      sid integer NOT NULL <auto increment?>
      sname varchar(30),
      PRIMARY KEY (sid)
      );

Any pointers are appreciated.

How does one escape an apostrophe in db2 sql

I'm looking for the db2 equivalent of T-SQL's:

INSERT INTO People (Surname) VALUES ('O''Hara');