Fast way for row counts in SQL

One of the best things I've ever seen:

select o.name, rows from sysobjects o inner join sysindexes i on o.id = i.id where i.indid < 2 and xtype='u' and o.name not in ('dtproperties')

Woo hoo...

Hey, ever had orphaned SQL server users... come on, tell the truth... YES YOU HAVE!

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Be careful when you use a web server as a generation machine

Make sure that you have separate WEB-INF dirs. One with WEB-DEVELOPMENT on and one with WEB-DEVELOPMENT off. If you don't do this, your web server will start and run, but will not be able to launch UBEs. Trust me. Just create a GEN-WEB-INF dir as a copy of the WEB-INF dir and alter the gen.bat to point to that dir. You can then customise the generator jbdj.ini to make things faster!!

Java generation, oh how ye sucks

I seem to have a lot of problems with Java Generation. I'm trying to understand why...

Get this, my latest problem set.

So, for some reason (I'm going to find out, trust me). My PY812 f989999 was generated with an OID and UID with VARCHAR. So, all of the deletes had loads of spaces in the front, and they found no rows... then the inserts failed... Meanwhile, I've had a PY webserver down for 2 days and have been too scared to promote anything to production in case the same thing was going to occur.

No matter what I did, truncate F989999 & F989998 - generate to a different location... Nothing seemed to work. I was at the end of my tether and saw an ancient call (and remembered it when it first came out) about changing OID and UID to CHAR - not VARCHAR.

Of course when I looked at my PY812 F989999 they were NVARCHAR. I don't know how it happened, but it did happen!

Note that there is also an option in the jdbj.ini
disableOracleNcharTrimming

Man, if I had have looked at that a little closer!

WAS and ports and locks and stuff

Well, I've just spent the morning recovering from a doozie of a problem. The JAS.INI for a web server was changed to have the [WEB DEVELOPMENT] section inserted with the WebDevelopment=true line added too. Holy moly - what a f*ck up!. This had the affect of letting people in, but they could not run reports. There were errors in the logs about:

20 Oct 2008 10:03:51,979 [SEVERE] MOIRS - [METADATA] Cause exception com.jdedwards.database.base.JDBException: [DATABASE_CONNECT_FAILED] Database Connection failed for DataSource SPEC_MSDE.
20 Oct 2008 10:03:51,979 [SEVERE] MOIRS - [METADATA] JDESpec error code = JDBj_SELECT_FAILURE com.peoplesoft.pt.e1.base.metadata.dataAccess.JDESpecException: JDBj select to spec RDB table failed for Spec Type RDASPEC.
20 Oct 2008 10:03:51,995 [SEVERE] MOIRS - [JAS] SubmitUBE.launchUBE() : Exception thrown during UBE submission! | Report Name : R0010P, Version Name : XJDE0001, Server Name : null, User Name : MOIRS com.jdedwards.system.kernel.LaunchUBEException: JDBj select to spec RDB table failed for Spec Type RDASPEC.
com.jdedwards.system.kernel.LaunchUBEException: JDBj select to spec RDB table failed for Spec Type RDASPEC.
at com.jdedwards.jas.SubmitUBEXML.packageSpecsAndSubmit(Unknown Source)
at com.jdedwards.jas.SubmitUBE.launchUBE(Unknown Source)
at com.jdedwards.jas.JDEOWDirect.jdeOWDirect(Unknown Source)
at com.jdedwards.jas.JDEOWDirect.processServerRequest(Unknown Source)
at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at com.jdedwards.jas.ServletProxy.callServerComponent(Unknown Source)
at com.jdedwards.jas.ServletProxy.servletProxy(Unknown Source)
at com.jdedwards.jas.ServletProxy.dispatch(Unknown Source)
at com.jdedwards.jas.net.NetConnection.callJAS(Unknown Source)
at com.jdedwards.jas.net.NetManager.processRequest(Unknown Source)

So, to find the problem I'm thinking, why is only this cluster member trying to load serialized objects from MSDE??? It could not be the manifest, it had to be the jdbj.ini (or jas.ini as in this case). I checked the [BOOTSTRAP] DS settings, I thought that it was going to be this, and it was not. I then stumbled on the JAS.INI and found the Web-Development setting.

Ok, great, I know why everything is f*cked, now.. Easy fix. Send everyone a SAW message. Restart the JVM - bing bang bong. The perfect crime...

But the instance would not start again, I kept getting the errors below:

[20/10/08 10:30:03:478 EST] 0000001f ChannelFramew E CHFW0029E: Error initializing chain DCS because of exception com.ibm.wsspi.channel.framework.exception.RetryableChannelException: Address already in use: bind
[20/10/08 10:30:03:509 EST] 0000001d TCPPort E TCPC0003E: TCP Channel TCP_5 initialization failed. The socket bind failed for host nsgshsjdnwb02.news.newslimited.local and port 9352. The port may already be in use.
[20/10/08 10:30:03:509 EST] 0000001d ChannelFramew E CHFW0029E: Error initializing chain DCS-Secure because of exception com.ibm.wsspi.channel.framework.exception.RetryableChannelException: Address already in use: bind
[20/10/08 10:30:08:494 EST] 0000001f TCPPort E TCPC0003E: TCP Channel TCP_5 initialization failed. The socket bind failed for host nsgshsjdnwb02.news.newslimited.local and port 9352. The port may already be in use.
[20/10/08 10:30:08:494 EST] 0000001f ChannelFramew E CHFW0029E: Error initializing chain DCS because of exception com.ibm.wsspi.channel.framework.exception.RetryableChannelException: Address already in use: bind
[20/10/08 10:30:08:525 EST] 0000001d TCPPort E TCPC0003E: TCP Channel TCP_5 initialization failed. The socket bind failed for host nsgshsjdnwb02.news.newslimited.local and port 9352. The port may already be in use.

So, I use the netstat command to find the pid that is locking the port:

netstat -b -v | findstr 9352

It tells me launchJVM.exe is doing this. Note that before being this smart, I tried restarting the nodeagent, then the network deployment node agent - of course none of this worked.

So, WTF??? This WEB-DEVELOPMENT section in the JAS.INI must control the LaunchJVM.exe process. This was locking all of my ports and making the machine a cluster f&ck. I did not want to bounce the entire machine, because half my users were happy in Prod.

Anyway, we are all happy now and I've got faith in windows netstat.

Some more generic tech notes that I need on the web

The below script will generate owner rename statements in SQLServer. I use them all the time for data refreshes. Just do database backup, restore and then run these puppies for DTA and CTL!

select 'EXEC sp_changeobjectowner ' + '''' + 'JDE_CRP.PRODDTA.' + so.name +
'''' + ', ' + '''' + 'CRPDTA' + '''' + ';'
from sysobjects so, sysusers su
where so.name like 'F%'
and so.xtype = 'U'
and su.uid = so.uid
and su.name = 'PRODDTA'

Get your oracle version from the SQL command like:

select banner from v$version

Installing peoplebooks?
PSOL documentation code FFFFFFFFF1

start ftp on linux
service vsftpd start

service smb start
/etc/samba/smb.conf


find and delete all files that are older than 6 days
find . -mtime +6 -print | xargs -i rm -f {}

Create table with SELET statement in SQL Server

any other database is

CREATE TABLE XX AS SELECT * FROM YYY;

No worries, simple... works in DB2, UDB & oracle... But SQL Server needs:


select * into NEW from OLD where sdsudfut2 = 'PY812' ;

What a crock!

RDP information

Do you ever get that message when you are RDP'ing to a client that it has too many open connections and you don't know what to do... well, here is a treat for you.

At the windows command line enter:

qwinsta /server:SERVERNAME

Will list all of the sessions, choose one that you can delete and then:

rwinsta /server:SERVERNAME ID_NUMBER

That'll get rid of the pesky session and let you log in - ripper!

remember to RDP to the console of a machine with the /console option after the machine name, that way you can share it with everyone (if you log in with the same user).