Getting error while deploying web service made using Axis2.1.3

Getting error while deploying web service made using Axis2.1.3

I have created a service archive file for the StockQuoteService example provided by the standard binary distribution o using ant generate.service method. I am deploying it in JBoss 4 and getting this error
Error: java.lang.NoSuchMethodError: javax.wsdl.Operation.getExtensibilityElements()Ljava/util/List; at org.apache.axis2.description.WSDL11ToAxisServiceBuilder.populateOperations(WSDL11ToAxisServiceBuilder.java:1241) at

The default "Version" service is working fine.

===============================


[ https://issues.apache.org/jira/browse/AXIS2-3282?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Ayondeep Datta closed AXIS2-3282.
---------------------------------

Resolution: Fixed
Fix Version/s: 1.3

I replaced wsdl4j.jar present in the lib of JBoss with the wsdl4j-1.6.2.jar and it resolved this issue.

ref

Second solution, Change the classloader for application to parentLast..
This will let the classloader have the piriority to load the application's jar first instead of server's jar.
:D

Webservices

Using Rational Developer to create a simple Web service and use it in a Web application

Turn EJB components into Web services

Best practices

Here are a few best practices for developing an EJB Web service:

* Avoid overusing Web services in your applications. Examine whether you really need to expose your EJB as a Web service.
* Use a coarse-grained session façade that encapsulates your business logic to be used as a Web service. Avoid exposing all your application's session beans as Web services.
* Examine properly whether you need either RPC-style or document-style Web services. RPC-style Web services are more efficient than document-style Web services. On the contrary, document style is more flexible because you can use schema.
* Make sure you design your Web service so that it creates minimal network traffic.
* Avoid maintaining any kind of state information in your EJB components that you expose as Web services.
* Use JAX-RPC data types as the method parameters for your Web service to give it interoperability with heterogeneous Web services. Avoid types such as Collection, HashMap, and Lists as parameters for your Web service if interoperability is important for your application.
* Many of the conventional best practices for J2EE applications (Web applications) are also relevant to EJB Web services. For example, avoid exposing an EJB component that involves long-running transactions as a Web service.
* Weigh your security requirements against performance, because security comes with a higher cost. The performance costs of end-to-end security are high.

Java Web Services, Part 3: Axis2 Data Binding link

ADMIN_CMD procedure

The ADMIN_CMD procedure is used by applications to run DB2 command 9 line processor (CLP) administrative commands using the SQL CALL 9 statement. 9 The procedure currently supports the following CLP commands:

use admin_cmd('')

ref

note. This only for build in for version 8.2 FixPak 4 (equivalent to version 8.1 FixPak 11) and DB2 Data Warehouse Edition Version 9.1.

Db2 version and Fixed pack level

1) run
db2support . -s

2) run
db2level

ref

Do u know tat we can query sql error code in db2 command line processor?

db2 => CALL runstats('unknown.table')@
SQL0443N Routine "STOLZE.RUNSTATS" (specific name "RUNSTATS") has returned an
error SQLSTATE with diagnostic text "Error -2306 returned by db2Runstats.".
SQLSTATE=38RS1

db2 => ? sql2306@

SQL2306N The table or index "" does not exist.

ref

Db2 import/export data

db2 import from datafile1.del of del replace into table1

IMPORT FROM "C:\aaaaaaaaa" OF IXF commitcount 10000 MESSAGES "C:\zzzzzz" INSERT INTO IBSADMIN.ARC_SETUP;
EXPORT TO "C:\aaaaaaaaa" OF IXF MESSAGES "C:\zzzzzz" SELECT * FROM IBSADMIN.ARC_SETUP;

import from date of DEL commitcount 10000 insert into mytable
EXPORT TO "C:\x" OF DEL MESSAGES "y" SELECT * FROM IBSADMIN.ARC_TABLE;

db2 export to mbuser.csv of del "select * from tibsadmin.mbuser"
db2 import
from mbuser.del of del "insert_update into tibsadmin.mbuser"

import

export

EXPORT in DEL forma get SQL3100W in DB2

I am using UDB V8.2 on AIX . When I do an EXPORT in DEL format , it gives a
SQL3100W on certain rows.

mainly, This warning occurs on rows that do not have any columns that are
over 254 chars.

In Command ref on page 365 second paragraph it says that if the char col is greater then 254 and using DEL format only you will get an error. IXF stores data diferently then DEL does, thus not effected by this limitation.

ref

Usefull sql script

sql to create backup table

CREATE TABLE UserBackup like User NOT LOGGED INITIALLY;
INSERT INTO UserBackup (SELECT * FROM User );

CSS in IE

1)IE not recognise css syntax if there is no space between the attribute.

table.title_bg
{
background:transparent url(/wps/images/ibs/hlbrib_bimb/sp_title_bg.gif)top left no-repeat;
}
==>work in firefox and opera but not IE (not space btw closing bracket and top)


table.title_bg
{
background:transparent url(/wps/images/ibs/hlbrib_bimb/sp_title_bg.gif) top left no-repeat;
}
==>work in IE

#.bodytext_9:hover {  TEXT-DECORATION: none}
=>IE do recognise this as comment. Use /* comment */

enable automatic JSP reloading in Portal

Follow these steps to enable automatic JSP reloading:

Open the file was_profile_root/config/cells/cell_name/applications/wps.ear/deployments/wps/wps.war/WEB-INF/ibm-web-ext.xmi

Find the following entry in this file:
xmlns:webappext="webappext.xmi" xmlns:webapplication="webapplication.xmi"
xmlns:commonext="commonext.xmi" xmlns:common="common.xmi"
xmi:id="IBM_WPS_Ext" reloadInterval="3" reloadingEnabled="false"
fileServingEnabled="true" directoryBrowsingEnabled="false"
serveServletsByClassnameEnabled="false" preCompileJSPs="false">

Change the value for reloadingEnabled to true.

Save the file.

Restart the portal server.

After completing these steps, JSPs are automatically reloaded when they are changed. However, to view changes to a JSP that is included by another (parent) JSP, you must also change the parent JSP to indicate that it must be reloaded by the server.


ref

SQL UNION and UNION ALL

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

Use "union all" when u wan all data for sql1 & sql2.
:)
"union" sometime might cause data missing if data from sql1 & sql2 are same.

ref

Looking up an EJB home with JNDI

Sometimes it is more convenient for an application to use a corbaname URL as the lookup name. Container-based JNDI clients and thin Java clients can use a corbaname URL. An example of a lookup with a corbaname URL is also included in this section

JNDI lookup from an application running in a container

  java.lang.Object ejbHome =
     initialContext.lookup(
        "java:comp/env/com/mycompany/accounting/AccountEJB");

JNDI lookup from an application that does not run in a container

java.lang.Object ejbHome = initialContext.lookup(
     "cell/nodes/Node1/servers/MyServer/com/mycompany/accounting/AccountEJB");

Server cluster
java.lang.Object ejbHome = initialContext.lookup(
     "cell/clusters/MyCluster/com/mycompany/accounting/AccountEJB");

Fixed qualified names
 java.lang.Object ejbHome = initialContext.lookup(
    "cell/persistent/com/mycompany/accounting/AccountEJB");

JNDI lookup with a corbaname URL
   java.lang.Object ejbHome = initialContext.lookup(
     "corbaname:iiop:someHost:2809#com/mycompany/accounting/AccountEJB");


ref

Example: Getting an initial context by setting the provider URL property

You can use two different provider URL forms with WebSphere Application Server's initial context factory:

• A CORBA object URL (new for J2EE 1.3)
• An IIOP URL

Using a CORBA object URL

env.put(Context.PROVIDER_URL, "corbaloc:iiop:myhost.mycompany.com:2809");

Using a CORBA object URL with multiple name server addresses

env.put(Context.PROVIDER_URL,
    "corbaloc::myhost1:9810,:myhost1:9811,:myhost2:9810");

Using a CORBA object URL from an non-WebSphere Application Server JNDI implementation

env.put(Context.INITIAL_CONTEXT_FACTORY,
     "com.somecompany.naming.TheirInitialContextFactory");
env.put(Context.PROVIDER_URL,
     "corbaname:iiop:myhost.mycompany.com:9810/NameServiceServerRoot");

Using an IIOP URL

env.put(Context.PROVIDER_URL, "iiop://myhost.mycompany.com:2809");

ref

Calling EJB deplyed in Websphere from Standalone Java client

Example :
java -cp /usr/WebSphere/AppServer/properties/:/usr/WebSphere/AppServer/lib/namingclient.jar
-jar TestJndiEJBLookup.jar

Might need this if include EjbClient.jar
\WebSphere\AppServer\lib\j2ee.jar
\WebSphere\AppServer\properties
\WebSphere\AppServer\lib\naming.jar;
\WebSphere\AppServer\lib\namingclient.jar;
\WebSphere\AppServer\java\jre\lib\server.jar


ref

How to list tables in another schema?

db2 > List tables for schema s1
db2> list tables for schema s2
db2> list tables for schema s3

or to list for all schemas

db2> list tables for all

or

db2 > Select TABSCHEMA, TABNAME, DEFINER from SYSCAT.TABLES where tabschema
IN ('S1','S2','S3')
tabschema is the schema name & Definer is the user name

EXTRA
to check store procedure
db2 > select PROCSCHEMA, PROCNAME from SYSCAT.PROCEDURES where procname = 'RPT_REGUSER_LOGIN_I' or procschema = 'IBSADMIN';

ref

Notes on creating a Java "jar" file for an application

If you have an appliation that you want to package so that it can be executed using a command like:

        java -jar myjarfile.jar arg1 arg2

you will need to create the ".jar" file properly. This means that the class containing the main method must be identified and that you have included any necessary support ".jar" files. You must also include a class path.

• JAR Files: http://java.sun.com/docs/books/tutorial/jar/
• JAR Guide: http://java.sun.com/j2se/1.4/docs/guide/jar/
• Note : http://www.bearcave.com/software/java/jar_notes.html
• OneJar: http://one-jar.sourceforge.net/

If you are using RAD, u may create "Application Client Project" with contain MyJarFileEar and MyJarFile project.

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

Cursor closes at COMMIT unless you use the WITH HOLD option on cursor.

Example
OPEN UPDATE_CURSOR WITH HOLD;

-- Declare cursor
FOR vl AS
c1 CURSOR with hold FOR
 SELECT customer_id as id,full_name from customer where full_name like 'test%'
DO
   update customer set status ='A' where customer_id = id;
   COMMIT;   <======= Can commit inside Loop if you choose WITH HOLD 

END FOR;
COMMIT;

ref ,
ibm

alter table in stored procedure

CREATE PROCEDURE ppg_gc (IN expiryTime INTEGER, IN batchSize INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN


declare v_stmt varchar(20);
--now store ur query inside this variable,then prepare the statement.

v_stmt = 'alter table emp drop primary key';
prepare S1 from v_stmt;
EXECUTE IMMEDIATE S1;

END @


ref

Hashmap examples used in jsp page?

Hi all,

Could anyone please point me to where i can find some examples using
hashmaps in a jsp with struts...
Instead of using a whole bunch of logic equal tags all the time, i need a
way to evaluate a collection, looking for a specific key value, then show or
hide the relevant input boxes on my jsp..
any help...please!!!???

See code sample below. The type in the iterate tag must be as shown, you
will then need to use a bean define to put your 'value' into the page
context and cast it to whatever type you have placed in your HashMap value.



<
logic:iterate id="list" name="gallery" property="artifacts"
type="java.util.Map.Entry">

<
bean:define id="artifact" name="list" property="value"
type="net.itwa.model.artifact.IArtifact"/>
<
bean:write name="artifact" property="description"/>
bean:write name="artifact" property="description">
logic:iterate>

ref

struts

Pass multiple params to html:link

// The only way i found

<bean:define id="param1" name="record" property="id"/>
<bean:define id="param2" name="record" property="language_id" />
<%
 java.util.HashMap params = new java.util.HashMap();
 params.put("record_id", param1);
 params.put("language_id", param2);
 pageContext.setAttribute("tempParams", params);
%>
>
 Hoja
html:link>

Pass more than one parameter to struts action using html:link tag

<bean:define id="param1" name="record" property="id"/>
<bean:define id="param2" name="record" property="language_id" />
<%
 java.util.HashMap params = new java.util.HashMap();
 params.put("record_id", param1);
 params.put("language_id", param2);
 pageContext.setAttribute("tempParams", params);
%>
>
 Hoja
html:link> 

struts-config.xml: Global Forward

<global-forwards>
 <forward name="error" path="/WEB-INF/jsp/error.jsp"/>
global-forwards> 

struts-config.xml: Basic Plugin

<plug-in className="listin.plugins.ListinPlugin">
 <set-property property="config_file" value="/WEB-INF/config/Listin.properties"/>
plug-in> 

struts-config.xml: Basic Action

  

<action path="/BuscarLDAP" type="listin.actions.BuscarLDAP">
 <forward name="success" path="/WEB-INF/jsp/resultados.jsp"/>
action>


ref

EJB query - subquery

code

ejb-jar.xml=>
select object(o) from UserAccount o
 where  o.ibuser.userId = ?1 and
 o.product.productCode in
 (select t.productCode from TransactionRuleProduct t
 where t.transactionRuleCode=?2
 and (t.isFromAcc =?3 or t.isToAcc=?4))


sql=>
select * from userAccount
where userid ='userxxx'
and productCode
in ( select productode from  TransactionRuleProduct
where transactionRuleCode=''
and (isFromAcc =1 or isTooAcc=1)
)

Different of ejbSelect and ejbFind

ejbSelect or find?

i found that the ejbSelectxxx and findxxx method are very similar,the only difference is that it seem like the ejbSelectxxx is not exposed to the client directly,you must wrapper it in a business method,then call the method from client,but i think i can use the findxxx method instead of the ejbSelectxxx method,who can tell me how can i decide when or where i use which method?

reply
First there is no relation between ejbSelect() and ejbFind(). You don't implement ejbFind() methods in CMP entity beans' classes, so you can't call an ejbSelect() method from an ejbFind() method.
Second, in CMP you don't write any persistence logic in your bean class. The container do this. But suppose you want to have a (home) business method. How will look the implementation of this method? No JDBC cals...
Here is the place of ejbSelect() methods. You tell the container how to implement this method just like with ejbFind() methods - with EJB QL. Then from your business method you call the ejbSelect() method. The most powerful thing here is that the result of this invocation can be anything - incl. persistent fields, not only Primary Keys.
Of course if you have relations with other entity beans the result will be an EJBObject ( or a collection ).

Finder and select methods use EJB QL queries to return objects and state information of entity beans using container-managed persistence.

A select method is similar to a finder method in the following ways:

1. A select method can return a local or remote interface (or a collection of interfaces).
2. A select method queries a database.
3. The deployment descriptor specifies an EJB QL query for a select method.
4. The entity bean class does not implement the select method.

However, a select method differs significantly from a finder method:

1. A select method can return a persistent field (or a collection thereof) of a related entity bean. A finder method can return only a local or remote interface (or a collection of interfaces).
2. Because it is not exposed in any of the local or remote interfaces, a select method cannot be invoked by a client. It can be invoked only by the methods implemented within the entity bean class. A select method is usually invoked by either a business or a home method.
3. A select method is defined in the entity bean class. For bean-managed persistence, a finder method is defined in the entity bean class, but for container-managed persistence it is not.

Code

public abstract class AddressBean implements javax.ejb.EntityBean {
...

// Customer home methods.
// These are wrappers of ejbSelect methods

public Collection ejbHomeQueryZipCodes(String state)
   throws FinderException   {
 return ejbSelectZipCodes(state);
}

public Collection ejbHomeQueryAll()
   throws FinderException  {
 return ejbSelectAll();
}

public CustomerLocal ejbHomeQueryCustomer(AddressLocal addr)
   throws FinderException   {
 return ejbSelectCustomer(addr);
}
...
}

ref1
ref2
ref3

Jasper Text Field Expression

How to do Text Field Expression in Jasper when I have more than one condition checking to output the textField?
Example...
I might have applicationResour
ceKey "key1","key2","key3", and i need to display it as "keyValue1","keyValue2","keyValue3"

Code

($F{key}).equals("key1") ?
"keyValue1" :
($F{key}).equals("key2")?
"keyValue2":
($F{key}).equals("key3")?
"keyValue3":
"-"

Doing LOOP in Stored Procedure

Code

DECLARE v_start int default 0;--Start month
DECLARE v_end int default 12;--End month

set v_start = 3;
set v_start = 6;
--[BEGIN] LOOP
L1: loop

if (v_start > v_end  ) then leave L1;
end if;
set v_start = v_start+1; 
--DO SOMETHING 
END FOR;
--[END] LOOP

Is there an equivalent DB2 syntax for the Oracle DECODE function?

QUESTION POSED ON:
Is there an equivalent DB2 syntax for the Oracle DECODE function?

EXPERT RESPONSE

Well, first of all, let's explain the Oracle DECODE expression for those not familiar with Oracle. A DECODE expression will look like this:

DECODE(expr,search,result,default)

There can be multiple search values and results, and default is optional. To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, null is returned. If expr and search contain character data, Oracle compares them using nonpadded comparison semantics. The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

So, basically, DECODE changes the value of an expression if the expression is equal to one of the values in the searched list. For example, this expression decodes the value deptno. If deptno is 10, the expression evaluates to 'ACCOUNTING'; if deptno is 20, it evaluates to 'RESEARCH'; etc. If deptno is not 10, 20, 30, or 40, the expression returns 'NONE'.

DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE')

In DB2 this can be accomplished using CASE expression. To write the equivalent of the above using DB2 you can write the following SQL statement:

SELECT CASE deptno
WHEN 10 THEN 'ACCOUNTING'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
WHEN 40 THEN 'OPERATIONS'
ELSE 'NONE'
END CASE
FROM EMP;

ref1,ref2

Query by pagination

ref

Code

-- IBM DB2
SELECT * FROM
(
SELECT a.* , rownumber() over () AS rn
FROM
(
SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
ORDER BY OrderDate DESC, ShippingDate DESC
) AS a
) AS rs
WHERE rs.rn between
(((pageNumber-1) * pageSize)+1)
AND
(pageNumber * pageSize)

Posted by AntiHuman at

How to trim leading zeroes when converting decimal() to char()

I need to convert a decimal field to char() but also trim the leading
zeroes. Any idea? I could not find any function to do that.

Re: How to trim leading zeroes when converting decimal() to char()

---

I'm sure there's a better way, but:

with sample table testdec:

create table testdec (col1 dec(10,5))

some sample data:

insert into testdec values (0005.500),(50.5),(12345.12345)

We then take a substring on a char of the first 5 characters (the
precision of the decimal value), convert it into an int to drop the
leading zeros, convert it back into a char, and then perform a right
trim (rtrim) on it to get rid of the padded blanks. We concatenate this
with the substring of the decimal separator and the digits to the right
of the decimal point (the scale), and convert this into a char.

select rtrim
( char(int(substr(char(col1),1,5))) )
concat
substr(char(col1),6,6) from testdec

1
-----------------
5.50000
50.50000
12345.12345

Like any example I provide, please test extensively with boundary
conditions.

ref

Code for conversion Dec(15,2):

 SELECT
rtrim(
char(
int(
substr(
char( cast('3333310.2' as dec(15,2)) ),
1,13)
)
)
)
concat
substr(
char( cast('3333310.2' as dec(15,2)) )
,14,3)
from sysibm.sysdummy1

RESULT=> 3333310.20

Or simpler solution

replace(replace(rtrim(ltrim(replace(char(), '0', ' '))), ' ', '0'), '.', '')
ref

RoA From Malaysia Beat T_T?

RoA From Malaysia Beat T_T?

ROA was the winner for Sino dota
sinodota.wordpress.com

Check this out http://www.meetyourmakers.com

Javascript Copy Selected Text Box Select All Highlight Text

The below link is
Javascript Copy Selected Text Box Select All Highlight Text

www.wallpaperama.com

But this will not work in firefox
because document.selection.createRange() is undefined
You need something like

Code:

 if (window.getSelection)
{
txt = window.getSelection();
foundIn = 'window.getSelection()';
}
else if (document.getSelection)
{
txt = document.getSelection();
foundIn = 'document.getSelection()';
}
else if (document.selection)
{
txt = document.selection.createRange().text;
foundIn = 'document.selection.createRange()';
}
else return;

Too bad later i found that window.getSelection will not work in textarea.. and yet this is bug for firefox according
http://www.squarefree.com

Code:

 function copyit(theField) {

var selectedText = document.selection;

var txt;
var foundIn;

if (window.getSelection)
{
txt=getSelectionInfo(theField);
//alert('ori='+theField.value.length);
   //alert('new='+txt.length);
if(txt.length==theField.value.length )
theField.value='';
}
else if (document.getSelection)
{
alert('2='+document.getSelection);
txt = document.getSelection();
foundIn = 'document.getSelection()';
theField.focus();
theField.value = txt;
}
else if (document.selection)
{
alert('3='+document.selection);
if (document.selection.type == 'Text') {

 txt = document.selection.createRange().text;
 alert('ori='+theField.value.length);
 alert('3='+txt.length);
 if(theField.value.length==txt.length)
 alert('clearAll!!');
 foundIn = 'document.selection.createRange()';
 theField.focus();
 theField.value = txt;
}
}
else return;

;

}
function getSelectionInfo(theField)
{
var rv = "";
var i,x;

x = theField;

 rv +=  x.value.substr(x.selectionStart, x.selectionEnd - x.selectionStart);

if(x.selectionStart==0 && x.selectionEnd==x.value.length)
return rv;
}

Selecting from an Insert, Update, Delete statement

Selecting from an Insert, Update, Delete statement

SELECT * FROM FINAL TABLE (INSERT INTO X VALUES …..)
This statement has three modes you can select from. The are

• OLD TABLE: before the data change statement operation has been executed
  
• NEW TABLE: just after the data change statement operation has been executed, but before both referential integrity evaluation and the firing of defined after-triggers.
  
• FINAL TABLE: after the operation has been executed, and after all referential integrity evaluations and after-triggers have been fired.

/blogs.ittoolbox.com

SQL6036N START or STOP DATABASE MANAGER command is already in progress.

db2stop
SQL6036N START or STOP DATABASE MANAGER command is already in
progress.
$


Is there any way I can fix this without restarting the OS. I am
running DB2 UDB 8.1 on AIX box?

ans:
Well ... you can either open up a support incident or if you want to
proceed at your own risk ... try using the db2stop force command and if
all else fails, db2_kill. Sounds like the previous stop is hung.

ref=webservertalk.com

FOR v2 AS
c2 CURSOR FOR
select distinct
a.userid,
RTRIM(char(MONTH(a.SESSIONSTARTON) ))
|| RTRIM(char(YEAR(a.SESSIONSTARTON))) as createdMth,
case
(
select distinct 1
from IBSADMIN.ActionAuditTrail aa,UserLoginAuditTrail ll
where
aa.LOGINTRAILID = ll.LOGINTRAILID
and aa.action ='auditAction.login'
and month(aa.createdOn) = 3
and year(aa.createdOn) = 2007
and ll.userid = a.userid
) when (1) then 1 else 0 end as status,
date(a.SESSIONSTARTON) as createdOn
from IBSADMIN.anonymousactionaudittrail a
where a.action ='auditAction.registration'
and month(a.SESSIONSTARTON) = 3
and year(a.SESSIONSTARTON) = 2007
and not exists
(
select 1
from ibsadmin.rpt_reguser_login rr
where rr.userid = a.userid
and rr.createdon =
RTRIM(char(MONTH(a.SESSIONSTARTON) ))
|| RTRIM(char(YEAR(a.SESSIONSTARTON)))
)and a.userid is not null
--end of Cursor

DO
--Inserting into Rpt table
INSERT INTO IBSADMIN.RPT_REGUSER_LOGIN
(USERID,CREATEDMTH,STATUS,CREATEDON)
VALUES (userid,createdMth,status,createdOn);
END FOR;
COMMIT;

Transact-SQL

query uses EXISTS

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');

query uses IN

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');

Here is the result set.

FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)

select i.userid,i.action,i.createdOn,count(*)
from
(

select
a.action,
l.userid,
RTRIM(char(MONTH(createdOn) )) || RTRIM(char(YEAR(createdOn))) as createdon
from IBSADMIN.ActionAuditTrail a,UserLoginAuditTrail l
where a.action ='auditAction.registration'
and a.LOGINTRAILID = l.LOGINTRAILID
and month(a.createdOn) = 3
and EXISTS
   (
   select 1
   from IBSADMIN.ActionAuditTrail aa,UserLoginAuditTrail ll
   where aa.action ='auditAction.login'
   and month(aa.createdOn) = 3
   and l.userid = ll.userid
   )

)
i group by  i.userid,i.action,i.createdOn

ref=technet.microsoft.com

DB2 Basics: Fun with Dates and Times

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

concatenate date or time values with other text?
LTRIM(RTRIM(char(MONTH(createdOn)) ))
|| LTRIM(RTRIM(char(YEAR(createdOn))))

output=> 32007

** LTRIM=Left Trim
 RTRIM=Right Trim


ref:developerWorks

Error creating simple procedure

Check this out.

"I'm trying to create a procedure and encountering all sorts of errors. I initially tried an example from Osborne's Complete DB2 Reference, an then resorted to something even simpler but still no luck.

CREATE PROCEDURE test (
IN x smallint,
OUT y smallint)
DYNAMIC RESULT SETS 0
READS SQL DATA
LANGUAGE SQL
BEGIN
SET y=x+1
END

returns the error
SQL0104N An unexpected token "END" was found following "QL BEGIN SET y=x+1 ". Expected tokens may include: "". LINE NUMBER=8. SQLSTATE=42601

Sounds like it wants a semi-colon, so I add one after x+1 and get two errors:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "SQL BEGIN SET y=x+1". Expected tokens may include: "". LINE NUMBER=8. SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN ". SQLSTATE=42601"

It all due to the reason:
How you are compiling that procedure.if you put it in an script file,
use @ as a signal to the end of procedure and use ; as the end of statements
with in the procedure, then use

db2 -td@ -f

any time you misses the t option db2 takes ; as the end of statement.

You will have to change the Statement Delimiter in your tool to @...
OK, at the bottom of the command editor window is a tiny textbox.
In English versions it says "termination character" in blue in front of
it. When you bring up command editor the text box should contain a
semicolon (;).
All you need to do is edit the textbox to be a "@" or perhaps a dollar
"$" sign.


By default, most tools use ;

DB2 Sql

How to convert VARCHAR to SMALLINT using DB2?
ans:CAST(B.mySmallIntAsString AS DECIMAL)


How to run sql script from db2 console?
db2 -tvf script_name.sql -z logfile_name.log

Prevent db2 attempts to process all the SQL in the file
and posts errors as they occur.
Use -s option. It will stop at the first error.
db2 -s -tvf